Querying XML using Oracle SQL developer

[elder81]

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

 

[Diancecht]

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

 

[taupirho]

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.

 

[elder81]

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)