Oracle XPATH question
- From: "Davey, Alan" <ddavey@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Mar 2006 16:42:05 -0500
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
Other related posts:
