Solved

Querying XML data producing incorrect results.

Posted on 2011-09-07
3
299 Views
Last Modified: 2012-05-12
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
Comment
Question by:cesarchavez
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36500504
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36500507
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
 

Author Closing Comment

by:cesarchavez
ID: 36503949
THanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question