On 3/16/06, Davey, Alan <ddavey@(protected)> 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 (See ORA-31012.ora-code.com): 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
On 3/16/06, <b class="gmail_sendername">Davey, Alan</b> <<a href="mailto :ddavey@(protected)">ddavey@(protected)</a>> wrote:<div><span class="gmail _quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb (204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <br>Hi,<br><br>The following is a portion of a sql query that extracts data from an XML<br>source. I would like to get the sequence/position number of the current<br>node, but I can't seem to find the right syntax.<br> <br> select extractvalue(value(x),'/SI/station')<br>, extractvalue(value(x),'/SI /days') days<br>, extractvalue(value(x),'/SI/startendtime') start_end_time<br>, extractvalue(value(x),'/SI/daypartname') dp<br>, extractvalue(value(x),'/SI /spotlength') len <br>, extractvalue(value(x),'/SI/programname') program<br>, null<br>from BR_GTT _AVAIL_IMPORT a<br>, table (xmlsequence(extract(xmltype(a.xml_content),'/TAM/SI' ))) x<br><br><br>I've tried adding:<br>, extractvalue(value(x),'position()') <br>to my select statement, but I get an Oracle error:<br>ORA-31012 (See ORA-31012.ora-code.com): Given XPATH expression not supported. I'm on Oracle <a href="http://10.1.0 .4">10.1.0.4</a><br><br>So it seems that Oracle understands what I'm trying to do, it just <br>doesn't support it. Is there another way to get the sequence of the /SI<br>node I am currently processing?<br><br>Although the above query does return the records in the same order as in<br>the XML file, the full query involves multiple table(xmlsequence()) <br>statements and does not return records in the same order as the xml<br>file . Even if the above query stood as is, I wouldn't rely on Oracle<br >returning the records in the same order as the xml file any how.<br><br>My intent is to use the value for future XPATH queries below that node <br>once the user has selected one of the records. Unfortunately, the XML<br>file I have to work with is crap and doesn't store any unique identifier<br>at the /SI node that could be used in place of node position.<br> <br> If anyone has any ideas to work around this problem I would appreciate<br>it. <br><br>Thanks.<br><br>Alan Davey<br><br></blockquote></div><br>Alan,<br><br clear="all">upgrade to 10.2?<br><br><ducking, running and laughing> <br><br>Paul<br>
