Re: Oracle XPATH question

On 3/16/06, Davey, Alan <ddavey_at_harris.com> wrote:
>
>
> 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
>
>

Alan,

upgrade to 10.2?

<ducking, running and laughing>

Paul

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