INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Querying XML using Oracle SQL developer

Querying XML using Oracle SQL developer

(OP)
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

RE: Querying XML using Oracle SQL developer

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

RE: Querying XML using Oracle SQL developer

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.

RE: Querying XML using Oracle SQL developer

(OP)
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)

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close