Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle XPATH question

RE: Oracle XPATH question

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 17 Mar 2006 10:23:30 +0100
Message-ID: <3FA1E6332C73EB4391F729E4EDB7CED9413070@amisnt30.AMIS.local>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Davey, Alan Sent: donderdag 16 maart 2006 22:42
To: oracle-l_at_freelists.org
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
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 17 2006 - 03:23:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US