Skip to Main Content
  • Questions

  • Use of xpath, support for namespaces

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 09, 2002 - 8:44 pm UTC

Last updated: March 08, 2004 - 11:37 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I am unable to use xpath since i have read somewhere that 9i does not support namespaces. Is this true ? What is the workaround?

Thanks.

and Tom said...

I asked Sean Dillon, our local XML guru, to take a look at this and here's what he said:
--

In Oracle9i release 1, you could not specify namespaces in XMLType functions. You can submit XPATH queries and you will get result whether or not they are in the default namespace or not. In release 2, this limitation has been lifted. Consider the following:

In a 9i release 1 database:
==================================================
SQL> connect sdillon/sdillon
Connected.

SQL> create table xml_documents(my_xml sys.xmltype);
Table created.

SQL> insert into xml_documents values (
2 sys.xmltype.createxml('<?xml version = ''1.0''?>
3 <ROWSET>
4 <ROW num="1">
5 <EMPNO>7369</EMPNO>
6 <ENAME>SMITH</ENAME>
7 <JOB>CLERK</JOB>
8 <MGR>7902</MGR>
9 <HIREDATE>12/17/1980 0:0:0</HIREDATE>
10 <SAL>800</SAL>
11 <DEPTNO>20</DEPTNO>
12 </ROW>
13 <ROW num="2">
14 <EMPNO>7499</EMPNO>
15 <ENAME>ALLEN</ENAME>
16 <JOB>SALESMAN</JOB>
17 <MGR>7698</MGR>
18 <HIREDATE>2/20/1981 0:0:0</HIREDATE>
19 <SAL>1600</SAL>
20 <COMM>300</COMM>
21 <DEPTNO>30</DEPTNO>
22 </ROW>
23 </ROWSET>'));
1 row created.

SQL> -- now an xml doc w/ a different namespace
SQL> insert into xml_documents values (
2 sys.xmltype.createxml('<?xml version = ''1.0''?>
3 <ROWSET xmlns="www.oracle.com/asktom.xml">
4 <ROW num="1">
5 <EMPNO>7369</EMPNO>
6 <ENAME>SMITH</ENAME>
7 <JOB>CLERK</JOB>
8 <MGR>7902</MGR>
9 <HIREDATE>12/17/1980 0:0:0</HIREDATE>
10 <SAL>800</SAL>
11 <DEPTNO>20</DEPTNO>
12 </ROW>
13 <ROW num="2">
14 <EMPNO>7499</EMPNO>
15 <ENAME>ALLEN</ENAME>
16 <JOB>SALESMAN</JOB>
17 <MGR>7698</MGR>
18 <HIREDATE>2/20/1981 0:0:0</HIREDATE>
19 <SAL>1600</SAL>
20 <COMM>300</COMM>
21 <DEPTNO>30</DEPTNO>
22 </ROW>
23 </ROWSET>'));
1 row created.

SQL> -- notice the second row of the result set has the fully qualified name:
SQL> select rownum, x.my_xml.extract('/ROWSET/ROW/EMPNO').getClobVal() empno
2 from xml_documents x;

ROWNUM EMPNO
---------- -----------------------------------------------------------------
1 <EMPNO>7369</EMPNO>
<EMPNO>7499</EMPNO>

2 <www.oracle.com/asktom.xml:EMPNO>7369</www.oracle.com/asktom.xml:EMPNO>
<www.oracle.com/asktom.xml:EMPNO>7499</www.oracle.com/asktom.xml:EMPNO>
==================================================


In a 9i release 2 database:
==================================================
SQL> connect sdillon/sdillon
Connected.
SQL> create table xml_documents(my_xml sys.xmltype);
Table created.

SQL> insert into xml_documents values (
2 sys.xmltype.createxml('<?xml version = ''1.0''?>
3 <ROWSET>
4 <ROW num="1">
5 <EMPNO>7369</EMPNO>
6 <ENAME>SMITH</ENAME>
7 <JOB>CLERK</JOB>
8 <MGR>7902</MGR>
9 <HIREDATE>12/17/1980 0:0:0</HIREDATE>
10 <SAL>800</SAL>
11 <DEPTNO>20</DEPTNO>
12 </ROW>
13 <ROW num="2">
14 <EMPNO>7499</EMPNO>
15 <ENAME>ALLEN</ENAME>
16 <JOB>SALESMAN</JOB>
17 <MGR>7698</MGR>
18 <HIREDATE>2/20/1981 0:0:0</HIREDATE>
19 <SAL>1600</SAL>
20 <COMM>300</COMM>
21 <DEPTNO>30</DEPTNO>
22 </ROW>
23 </ROWSET>'));
1 row created.

SQL> -- again, an xml document with a different namespace
SQL> insert into xml_documents values (
2 sys.xmltype.createxml('<?xml version = ''1.0''?>
3 <ROWSET xmlns="www.oracle.com/asktom.xml">
4 <ROW num="1">
5 <EMPNO>7369</EMPNO>
6 <ENAME>SMITH</ENAME>
7 <JOB>CLERK</JOB>
8 <MGR>7902</MGR>
9 <HIREDATE>12/17/1980 0:0:0</HIREDATE>
10 <SAL>800</SAL>
11 <DEPTNO>20</DEPTNO>
12 </ROW>
13 <ROW num="2">
14 <EMPNO>7499</EMPNO>
15 <ENAME>ALLEN</ENAME>
16 <JOB>SALESMAN</JOB>
17 <MGR>7698</MGR>
18 <HIREDATE>2/20/1981 0:0:0</HIREDATE>
19 <SAL>1600</SAL>
20 <COMM>300</COMM>
21 <DEPTNO>30</DEPTNO>
22 </ROW>
23 </ROWSET>'));
1 row created.

SQL> -- notice the second row has no data due to the namespace
SQL> select rownum, x.my_xml.extract('/ROWSET/ROW/EMPNO').getClobVal() empno
2 from xml_documents x;

ROWNUM EMPNO
---------- ----------------------------------------------------------------------
1 <EMPNO>7369</EMPNO>
<EMPNO>7499</EMPNO>

2

SQL> -- but now we can use a second parameter in the EXTRACT() function to
SQL> -- specify a namespace for our XPath expression:
SQL> select rownum,
2 x.my_xml.extract('/ROWSET/ROW/EMPNO',
3 'xmlns="www.oracle.com/asktom.xml"').getClobVal() empno
4 from xml_documents x;

ROWNUM EMPNO
---------- -------------------------------------------------------------------
1
2 <foo:EMPNO xmlns:foo="www.oracle.com/asktom.xml">7369</foo:EMPNO>
<foo:EMPNO xmlns:foo="www.oracle.com/asktom.xml">7499</foo:EMPNO>

This second parameter applies to all the 9i release 2 XMLType functions, such as EXTRACT(), EXTRACTVALUE(), EXISTSNODE(), and UPDATEXML().

There is namespace support THROUGHOUT the XML Developer's Kit as well, but considering your question the XMLType XPATH/namespace support was what you were interested in. For more information about namespace support in the Oracle XML Developer's Kits (XDKs), see the Oracle Technology Network (OTN) at </code> http://otn.oracle.com/tech/xml/content.html 

(You need an account to use OTN, but the accounts are free you just need to take the few minutes to register an account here:
http://otn.oracle.com/membership 

Hope that helps!
____________________________________________________________________
Sean Dillon - Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming"
http://www.amazon.com/exec/obidos/ASIN/186100690X <code>

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Getting data from XML file and Saving in database

Chaitanya Sravanth P, September 30, 2002 - 3:14 am UTC

I've a scenario for which I need help.
My client will be sending his data through an XML file. Those XML files will be uploaded by our applications admin to a particular folder and the path to that folder is stored in the database. What I should do is, basing the path specified, I should go to the XML file location, search for the data inside those files and extract them and store in a seperate table (different values in different columns). I'm new to the SQL - XML concept. Can you please guide me regd. this.

Tom Kyte
September 30, 2002 - 7:26 am UTC

goto otn.oracle.com

click on technologies -> XML

lots of stuff there. Tons of it.

Problem with updating xml

A reader, March 08, 2004 - 11:37 pm UTC

Hi Tom,

I searched a lot for updatexml examples in your site and also in otn. But i did not find any related to my problem.
i will be gratefule if you can help me out.

I have registered an xml schema in the database and i created a table in whcih one column is schema based storage.
Then i inserted the xml data into the table.

When i query the column using the below query, i get such an output.

SELECT EXTRACTVALUE(VALUE(x), '/StepGeneralColumn[@DataType="KonfigFile"]/text()', 'xmlns="</code> http://www.w3.org/2001/XMLSchema/XMLAblDaten.xsd"' 
  FROM TabL_FileContent
, TABLE(XMLSEQUENCE(EXTRACT(SequenceData, '/MEPSequenceData//StepGeneralColumn', 'xmlns="
http://www.w3.org/2001/XMLSchema/XMLAblDaten.xsd"' <code>)) x
WHERE FileID = 235633
/

c:\konfig1.cfg
c:\konfig2.cfg
c:\konfig3.cfg
c:\konfig4.cfg

Which means i get all the data. No i want to update the same XPATH using UpdateXML.
I want the remove the physical path from the filenames after it is stored in the database. So it should become.

konfig1.cfg
konfig2.cfg
konfig3.cfg
konfig4.cfg

But when i use UpdateXML, it updates all the instances with the same value. So all become konfig4.cfg. But i want to update for each instance.

some thing like

update table set column = konfig1.cfg where column = c:\konfig1.cfg.






how to convert xmltype to long

raul, May 16, 2005 - 1:03 pm UTC


Using XPath when you don't know the namespace prefix

Martin, June 09, 2005 - 10:07 am UTC 

Hi Tom / Sean,

Hope you can answer this question for me. Basically, I'm writing basic XML EXTRACT routines using XPath and was wondering how you solve the problem below.

Let's say I have the following setup :

SQL> DECLARE
  2    x   XMLTYPE := XMLTYPE('<x:a xmlns:x="x.y.z">10</x:a>');
  3  BEGIN
  4    FOR i IN ( SELECT EXTRACTVALUE(VALUE(t), '/x:a', 'xmlns:x="x.y.z"') a
  5                 FROM TABLE(XMLSEQUENCE(x)) t )
  6    LOOP
  7      dbms_output.put_line(i.a);
  8    END LOOP;
  9  END;
 10  /
10

PL/SQL procedure successfully completed.

But, I need to be able to handle the situation where the namespace prefix "x" changes, (but of course the namespace itself is constant), i.e.

<y:a xmlns:y="x.y.z">10</y:a>

The trouble is that the XPath expression still references x:a. 
Is it possible to define an xpath expression which effectively extracts the /a but in namespace "x.y.z" regardless of prefix, in such as way that I don't have to "pre-process" the XML to work out what the prefix(es) are?

Other languages allow you to physically specify the namespace against the "x.y.z" URI, but the XMLTYPE methods
don't seem to?

Thanks in advance
Martin.

Query to extract a node with different or no prefixes ?

ADB, May 18, 2006 - 11:25 am UTC

Is it possible to write an xml query to extract a node with different or no prefixes as asked by previous reviewer?

I am logging in a request coming to a webservice into an XMLType column in Oracle. The requests could come in with or without namespace or with a fixed namespace but with different prefixes. Is it possible to write a query that would select all instances of a node type irrespective of its namespace declaration?

Thank you,

How to extract XML-nodes via XML-attributes?

Max, August 27, 2006 - 2:09 am UTC

Hi Tom,

one can access the first XML-element like that:

with MySet as
(
select xmltype( '<MyDoc>
<MyElem MyAttr="ABC">123</MyElem>
<MyElem MyAttr="XYZ">456</MyElem>
</MyDoc>' ) MyXML
from dual
)

select extractvalue( value( C ), '*' ) MyVal
from MySet S,
table( xmlsequence( extract( S.MyXML, '//MyDoc/MyElem[1]' ) ) ) C ;

But is there a way to access those XML-elements through their XML-attributes as well, something like:

... xmlsequence( extract( S.MyXML, '//MyDoc/MyElem[MyAttr="ABC"] ...

(which doesn't work with 9iR2)?

Yes, there is ...

Max2Max ;o), September 14, 2006 - 12:38 pm UTC

with MySet as
(
select xmltype( '<MyDoc xmlns:myns="any">
<MyElem MyAttr="ABC">123</MyElem>
<MyElem MyAttr="XYZ">456</MyElem>
</MyDoc>' ) MyXML
from dual
)

select extractvalue( value( C ), '*','xmlns:myns="any"' ) MyVal
from MySet S,
table( xmlsequence( extract( S.MyXML,
'//MyElem[@MyAttr="ABC"]',
'xmlns:myns="any"' ) ) ) C ;

with MySet as
(
select xmltype( '<MyDoc xmlns:myns="any">
<MyElem myns:MyAttr="ABC">123</MyElem>
<MyElem myns:MyAttr="XYZ">456</MyElem>
</MyDoc>' ) MyXML
from dual
)

select extractvalue( value( C ), '*','xmlns:myns="any"' ) MyVal
from MySet S,
table( xmlsequence( extract( S.MyXML,
'//MyElem[@myns:MyAttr="ABC"]',
'xmlns:myns="any"' ) ) ) C ;


Relative XPATH

Michael Friedman, February 02, 2007 - 11:57 am UTC

Is there any way to use relative XPATHs with Oracle XML?

Consider an XML document... for example,

<doc>
<tag1/>
<xpath_exp>RELATIVE_XPATH_EXPRESSION</xpath_exp>
</doc>

We need to find the xpath_exp element, extract the RELATIVE_XPATH_EXPRESSION, and then use that to identify another node in the document where we will do another transformation.

UpdateXML documentation says "You can specify an absolute XPath_string with an initial slash or a relative XPath_string by omitting the initial slash. If you omit the initial slash, the context of the relative path defaults to the root node.".

If the context of the relative path defaults to the root node how can you override this and use a different context?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here