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

--
http://www.freelists.org/webpage/oracle-l