RE: Oracle XPATH question

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

Just did a small test on one of my XML DB databases...

SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

5 rows selected.

SQL> SELECT extractvalue(value(v),'position()')   2 FROM hgo.hgo011_detam t,

  3         TABLE( XMLSEQUENCE( EXTRACT(t.object_value,
  4         '/wwb:WW-HISTORIE/R801FINR/R801-NUM-FIS',
  5         'xmlns:wwb="http://www.uwv.nl/ww/historie/detam/WWBase"')))

v
  6 ;
SELECT extractvalue(value(v),'position()') *
ERROR at line 1:
ORA-31012: Given XPATH expression not supported

So sorry to say, it is still not supported on 10.2.0.2.0

Marco  

-----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:02:04 CST