Hi all,

First let me set the scene. I am very new to XML and got a reasonable understanding of sql.

I have a database that amongst other things has a column in a table that contains the following XML.

"<Request action="Update">
<Personality>
<Identity><PersonIdentity><PersonNumber>187347</PersonNumber>
</PersonIdentity>
</Identity>
<JobAssignmentData><JobAssignment><JobAssignmentDetailsData><JobAssignmentDetails><BaseWageHourly></BaseWageHourly>
<DeviceGroupName>NW44</DeviceGroupName>
<PayRuleName>RT - 30 Min Auto Ded >39</PayRuleName>
</JobAssignmentDetails>
</JobAssignmentDetailsData>
<PrimaryLaborAccounts><PrimaryLaborAccount><EffectiveDate>15/08/2006</EffectiveDate>
<LaborAccountName>Food/North West RT/North West Region 4 RT/North West Area 44 RT/Late Shop Ellenbrook/-/-</LaborAccountName>
</PrimaryLaborAccount>
</PrimaryLaborAccounts>
</JobAssignment>
</JobAssignmentData>
<PersonInformationData><PersonInformation><BadgeAssignments><BadgeAssignment><BadgeNumber>187347</BadgeNumber>
<EffectiveDate>16/06/2006</EffectiveDate>
</BadgeAssignment>
</BadgeAssignments>
<CustomDataList><CustomData><CustomDataTypeName>AIS</CustomDataTypeName>
<Text></Text>
</CustomData>
</CustomDataList>
<EmploymentStatusList><EmploymentStatus><EffectiveDate>16/06/2006</EffectiveDate>
<EmploymentStatusName>Active</EmploymentStatusName>
</EmploymentStatus>
</EmploymentStatusList>
<ExpectedHoursList><ExpectedHours><Quantity>25</Quantity>
<TimePeriodTypeName>Weekly</TimePeriodTypeName>
</ExpectedHours>
</ExpectedHoursList>
<Identity><PersonIdentity><PersonNumber>187347</PersonNumber>
</PersonIdentity>
</Identity>
<PersonData><Person><AccrualProfileName>Holiday & Lieu Time</AccrualProfileName>
<BirthDate>03/07/1960</BirthDate>
<FingerRequiredFlag>True</FingerRequiredFlag>
<FirstName>Tracey</FirstName>
<HireDate>16/06/2006</HireDate>
<LastName>Howard</LastName>
<MiddleInitial></MiddleInitial>
<PersonNumber>187347</PersonNumber>
</Person>
</PersonData>
<PersonLicenseTypes><PersonLicenseType><ActiveFlag>TRUE</ActiveFlag>
<LicenseTypeName>Workforce_Timekeeper_Employee</LicenseTypeName>
</PersonLicenseType>
</PersonLicenseTypes>
<PostalAddresses><PostalAddress><City></City>
<ContactTypeName>5</C"

What I would like to is extract the personnumber shown in the first row. Have done muh research and I am trying to use the following script,

select EXTRACTVALUE(xmltype(xmlreqbody), '/Personality/Identity/PersonIdentity/PersonNumber')
from failedxmlbody

This will eventually be incorporated into a larger select statement that I have completed already. It's just this bit I need to get working.

Go easy on me, as I am new to this.

Thanks

So I guess it's not working, right? What error are you getting? Any help here?

Btw, do you consider a good idea to store XML files on a database?

Cheers,
Dian

Well, the XML fragment is obviously not well formed so I think you will have problems
a) Storing it as XML and
b) using Oracle XML built in functions to process it

One solution is to store is as a CLOB and use regular string fucntions to parse it. Its a bit clunky and might not scale too well but here goes:-

SQL> create table failedxml(test clob)
  2  /

Table created.

  1  insert into failedxml(test)
  2  values(
  3  to_clob
  4  (
  5  '<Request action="Update">
  6  <Personality>
  7  <Identity><PersonIdentity><PersonNumber>187347</PersonNumber>
  8  </PersonIdentity>
  9  </Identity>
 10  <JobAssignmentData><JobAssignment><JobAssignmentDetailsData><JobAssignmentD
etails><BaseWageHourly></BaseWageHourly>
 11  <DeviceGroupName>NW44</DeviceGroupName>
 12  <PayRuleName>RT - 30 Min Auto Ded >39</PayRuleName>
 13  </JobAssignmentDetails>
 14  </JobAssignmentDetailsData>
 15  <PrimaryLaborAccounts><PrimaryLaborAccount><EffectiveDate>15/08/2006</Effec
tiveDate>
 16  <LaborAccountName>Food/North West RT/North West Region 4 RT/North West Area
 44 RT/Late Shop Ellenbrook/-/-</LaborAccountName>
 17  </PrimaryLaborAccount>
 18  </PrimaryLaborAccounts>
 19  </JobAssignment>
 20  </JobAssignmentData>
 21  <PersonInformationData><PersonInformation><BadgeAssignments><BadgeAssignmen
t><BadgeNumber>187347</BadgeNumber>
 22  <EffectiveDate>16/06/2006</EffectiveDate>
 23  </BadgeAssignment>
 24  </BadgeAssignments>
 25  <CustomDataList><CustomData><CustomDataTypeName>AIS</CustomDataTypeName>
 26  <Text></Text>
 27  </CustomData>
 28  </CustomDataList>
 29  <EmploymentStatusList><EmploymentStatus><EffectiveDate>16/06/2006</Effectiv
eDate>
 30  <EmploymentStatusName>Active</EmploymentStatusName>
 31  </EmploymentStatus>
 32  </EmploymentStatusList>
 33  <ExpectedHoursList><ExpectedHours><Quantity>25</Quantity>
 34  <TimePeriodTypeName>Weekly</TimePeriodTypeName>
 35  </ExpectedHours>
 36  </ExpectedHoursList>
 37  <Identity><PersonIdentity><PersonNumber>187347</PersonNumber>
 38  </PersonIdentity>
 39  </Identity>
 40  <PersonData><Person><AccrualProfileName>Holiday and Lieu Time</AccrualProfi
leName>
 41  <BirthDate>03/07/1960</BirthDate>
 42  <FingerRequiredFlag>True</FingerRequiredFlag>
 43  <FirstName>Tracey</FirstName>
 44  <HireDate>16/06/2006</HireDate>
 45  <LastName>Howard</LastName>
 46  <MiddleInitial></MiddleInitial>
 47  <PersonNumber>187347</PersonNumber>
 48  </Person>
 49  </PersonData>
 50  <PersonLicenseTypes><PersonLicenseType><ActiveFlag>TRUE</ActiveFlag>
 51  <LicenseTypeName>Workforce_Timekeeper_Employee</LicenseTypeName>
 52  </PersonLicenseType>
 53  </PersonLicenseTypes>
 54  <PostalAddresses><PostalAddress><City></City>
 55  <ContactTypeName>5</C
 56  ')
 57* )
SQL> /

1 row created.

  1  select
  2  substr(test,instr(test,'<PersonNumber>',1,1)+14,
  3  instr(test,'</PersonNumber>',1,1)-instr(test,'<PersonNumber>',1,1) -14)
  4* from failedxml
SQL> /

SUBSTR(TEST,INSTR(TEST,'<PERSONNUMBER>',1,1)+14,INSTR(TEST,'</PERSONNUMBER>',1,1
--------------------------------------------------------------------------------
187347





  

In order to understand recursion, you must first understand recursion.

Thank you to Taupiro.

I actually ended up using a similar script to yours before I saw your reply.

My script is as below:

XMLREQBODY,substr(xmlreqbody,(Instr(xmlreqbody,'PersonNumber')+13),(Instr(xmlreqbody,'/PersonNumber'))-(Instr(xmlreqbody,'PersonNumber')+14)) as PersonNumber

(This is part of a much bigger select statement)