Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple rows/values

444743Sep 13 2008 — edited Sep 16 2008

How do I extract values if one of the tabs can occur multiple times?

Here is my xml structure:
JobPositionPosting > dsSearch > dsId > "technis"
JobPositionPosting > dsSearch > dsLocation > dsLongitude > "-889558"
JobPositionPosting > dsSearch > dsLocation > dsLatitude > "404743"
JobPositionPosting > JobPosting > JobPositionPosting > JobPositionPostingId > "1001117543"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "automation"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "c"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "c#"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "c++"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "clearcase"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "design"
JobPositionPosting > JobPosting > JobSpecific > DigestedSkills > Skill > "development"

If I run the query below it runs just fine.
select
extractValue(object_value, '/JobPositionPosting/dsSearch/dsId') dsId ,
extractValue(object_value, '/JobPositionPosting/dsSearch/dsLocation/dsLongitude') LONGIT ,
extractValue(object_value, '/JobPositionPosting/dsSearch/dsLocation/dsLatitude') Lat
from DANB.invoicexml_tbl

But when I try to include skills it blows up with

select
extractValue(object_value, '/JobPositionPosting/dsSearch/dsId') dsId ,
extractValue(object_value, '/JobPositionPosting/dsSearch/dsLocation/dsLongitude') LONGIT ,
extractValue(object_value, '/JobPositionPosting/dsSearch/dsLocation/dsLatitude') Lat,
extractValue(object_value, '/JobPositionPosting/JobPosting/JobSpecific/DigestedSkills/Skill') skill
from DANB.invoicexml_tbl
ORA-19025: EXTRACTVALUE returns value of only one node

Looks like having skil there multiple times is a problem.

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 14 2008

Added on Sep 13 2008

#performance-availability, #xml, #xml-db

3 comments

602 views