Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Querying XML data producing incorrect results.

Hi Everyone.

I am trying to query some xml data that i have in a clob in my oracle database.

the xml file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Race>
  <RaceID>1</RaceID>
  <RaceType>Match</RaceType>
  <Course>
    <CompoundMark CompoundMarkID="1">
      <Mark SeqID="1" Lat="38.697406" Lng="-9.401273">  </Mark>
    </CompoundMark>
    <CompoundMark CompoundMarkID="2">
      <Mark SeqID="1" Lat="38.697301" Lng="-9.402718">   </Mark>
      <Mark SeqID="2" Lat="38.697301" Lng="-9.402718">   </Mark>
    </CompoundMark>
    <CompoundMark CompoundMarkID="3">
      <Mark SeqID="1" Lat="38.694322" Lng="-9.406006">   </Mark>
    </CompoundMark>
    
  </Course>
  
</Race>

Open in new window


The query i am using is


SELECT EXTRACTVALUE(VALUE(r), '/Race/RaceID') Raceid,
       EXTRACTVALUE(VALUE(d), '/CompoundMark/@CompoundMarkID') MarkID,
       EXTRACTVALUE(VALUE(e), '//Mark/@Lat') Lat,
       EXTRACTVALUE(VALUE(e), '//Mark/@Lng') Lng,
       EXTRACTVALUE(VALUE(e), '//Mark/@SeqID') SeqID
  FROM ext_clob_table,
       TABLE(XMLSEQUENCE(EXTRACT(xmltype(text), '/Race'))) r,
       TABLE(XMLSEQUENCE(EXTRACT(r.COLUMN_VALUE, '//CompoundMark'))) d,
       TABLE(XMLSEQUENCE(EXTRACT(r.COLUMN_VALUE, '//Mark')))     e
       

The output i am getting is

RACEID      MARKID      LAT      LNG      SEQID
1      1      38.697406      -9.401273 1
1      1      38.697301      -9.402718       1
1      1      38.697301      -9.402718       2
1      1      38.694322      -9.406006       1
1      2      38.697406      -9.401273       1
1      2      38.697301      -9.402718       1
1      2      38.697301      -9.402718       2
1      2      38.694322      -9.406006       1
1      3      38.697406      -9.401273       1
1      3      38.697301      -9.402718       1
1      3      38.697301      -9.402718       2
1      3      38.694322      -9.406006       1

where as what i want is

RACEID      MARKID      LAT      LNG      SEQID
1      1      38.697406      -9.401273 1
1      2      38.697301      -9.402718       1
1      2      38.697301      -9.402718       2
1      3      38.694322      -9.406006       1


Please help!
0
cesarchavez
Asked:
cesarchavez
  • 2
1 Solution
 
sdstuberCommented:
SELECT EXTRACTVALUE(VALUE(r), '/Race/RaceID') raceid,
       EXTRACTVALUE(VALUE(d), '/CompoundMark/@CompoundMarkID') markid,
       EXTRACTVALUE(VALUE(e), '/Mark/@Lat') lat,
       EXTRACTVALUE(VALUE(e), '/Mark/@Lng') lng,
       EXTRACTVALUE(VALUE(e), '/Mark/@SeqID') seqid
  FROM ext_clob_table,
       TABLE(XMLSEQUENCE(EXTRACT(xmltype(text), '/Race'))) r,
       TABLE(XMLSEQUENCE(EXTRACT(r.COLUMN_VALUE, '//CompoundMark'))) d,
       TABLE(XMLSEQUENCE(EXTRACT(d.column_value, '//Mark'))) e
0
 
sdstuberCommented:
the difference is the e reference


old  -  TABLE(XMLSEQUENCE(EXTRACT(r.COLUMN_VALUE, '//Mark')))     e

new -  TABLE(XMLSEQUENCE(EXTRACT(d.column_value, '//Mark'))) e
0
 
cesarchavezAuthor Commented:
THanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now