RE: Oracle XPATH question
- To: <ddavey@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Mar 2006 10:23:30 +0100
The following is working on my 10.2.0.2 database, but this probably
won't help you...but maybe you could think of a workaround based on
this.
SQL> select count(*)
2 FROM hgo.hgo011_detam t,
3 TABLE( XMLSEQUENCE( EXTRACT(t.object_value,
4 '//R810-BED-INK-UAV-EWW[position() > 1]',
5 'xmlns:wwb="http://www.uwv.nl/ww/historie/detam/WWBase";')))
v
6 ;
COUNT(*)
---------
3926
1 row selected.
SQL> select count(*)
2 FROM hgo.hgo011_detam t,
3 TABLE( XMLSEQUENCE( EXTRACT(t.object_value,
4 '//R810-BED-INK-UAV-EWW[position() <= 1]',
5 'xmlns:wwb="http://www.uwv.nl/ww/historie/detam/WWBase";')))
v
6 ;
COUNT(*)
---------
7356
1 row selected.
SQL> select count(*)
2 FROM hgo.hgo011_detam t,
3 TABLE( XMLSEQUENCE( EXTRACT(t.object_value,
4 '//R810-BED-INK-UAV-EWW',
5 'xmlns:wwb="http://www.uwv.nl/ww/historie/detam/WWBase";')))
v
6 ;
COUNT(*)
---------
11282
1 row selected.
SQL> select 3926+7356 from dual;
3926+7356
---------
11282
1 row selected.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Davey, Alan
Sent: donderdag 16 maart 2006 22:42
To: oracle-l@xxxxxxxxxxxxx
Subject: Oracle XPATH question
Hi,
The following is a portion of a sql query that extracts data from an XML
source. I would like to get the sequence/position number of the current
node, but I can't seem to find the right syntax.
select extractvalue(value(x),'/SI/station')
, extractvalue(value(x),'/SI/days') days ,
extractvalue(value(x),'/SI/startendtime') start_end_time ,
extractvalue(value(x),'/SI/daypartname') dp ,
extractvalue(value(x),'/SI/spotlength') len ,
extractvalue(value(x),'/SI/programname') program , null from
BR_GTT_AVAIL_IMPORT a , table
(xmlsequence(extract(xmltype(a.xml_content),'/TAM/SI'))) x
I've tried adding:
, extractvalue(value(x),'position()')
to my select statement, but I get an Oracle error:
ORA-31012: Given XPATH expression not supported. I'm on Oracle 10.1.0.4
So it seems that Oracle understands what I'm trying to do, it just
doesn't support it. Is there another way to get the sequence of the /SI
node I am currently processing?
Although the above query does return the records in the same order as in
the XML file, the full query involves multiple table(xmlsequence())
statements and does not return records in the same order as the xml
file. Even if the above query stood as is, I wouldn't rely on Oracle
returning the records in the same order as the xml file any how.
My intent is to use the value for future XPATH queries below that node
once the user has selected one of the records. Unfortunately, the XML
file I have to work with is crap and doesn't store any unique identifier
at the /SI node that could be used in place of node position.
If anyone has any ideas to work around this problem I would appreciate
it.
Thanks.
Alan Davey
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
