Or in other words how to use namespace based on the “new” method (Oracle 10.2 and above) or the method that can be used on older versions (and newer 
of course). This post is just a reminder, because this is always a hassle (and I never can remember when I need to).
The post is based on a XMLDB Forum question here.
XMLTable
SQL> WITH table1 AS 2 (SELECT xmltype( 3 '<abc xmlns:ns1="http://a.b.org/"> 4 <ns1 :abtest>true</ns1> 5 <ns1 :type>a</ns1> 6 <ns1 :def xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ns1:ORD="0"> 7 </ns1><ns1 :oper>SFO</ns1> 8 <ns1 :lmt id="Director">limit1</ns1> 9 10 <ns1 :def xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ns1:ORD="1"> 11 </ns1><ns1 :oper>boston</ns1> 12 <ns1 :lmt id="Management">limit2</ns1> 13 14 </abc>' 15 ) xmlcol FROM dual 16 ) 17 SELECT u.VALUE 18 , u.attribute 19 FROM table1 20 , XMLTable(XMLNAMESPACES ('http://a.b.org/' AS "X"), 21 '/abc/X:def' PASSING xmlcol 22 COLUMNS VALUE VARCHAR2(20) PATH 'X:oper', 23 attribute VARCHAR2(20) PATH 'X:lmt/@id' ) u 24 ; VALUE ATTRIBUTE -------------------- -------------------- SFO Director boston Management |
table(XMLSequence(extract())
SQL> col VALUE FOR a20 SQL> col attribute FOR a20 SQL> WITH table1 AS 2 (SELECT xmltype( 3 '<abc xmlns:ns1="http://a.b.org/"> 4 <ns1 :abtest>true</ns1> 5 <ns1 :type>a</ns1> 6 <ns1 :def xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ns1:ORD="0"> 7 </ns1><ns1 :oper>SFO</ns1> 8 <ns1 :lmt id="Director">limit1</ns1> 9 10 <ns1 :def xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ns1:ORD="1"> 11 </ns1><ns1 :oper>boston</ns1> 12 <ns1 :lmt id="Management">limit2</ns1> 13 14 </abc>' 15 ) xmlcol FROM dual 16 ) 17 SELECT ExtractValue(column_Value, '/ns1:def/ns1:oper','xmlns:ns1="http://a.b.org/"') AS "VALUE", 18 ExtractValue(column_Value, '//ns1:def/ns1:lmt/@ns1:id','xmlns:ns1="http://a.b.org/"') AS "ATTRIBUTE" 19 FROM table1 t1 , 20 TABLE(xmlsequence(EXTRACT(t1.xmlcol,'/abc/ns1:def','xmlns:ns1="http://a.b.org/"'))) v; VALUE ATTRIBUTE -------------------- -------------------- SFO Director boston Management |
5 comments
Skip to comment form ↓
Amos
8 January, 2009 at 3:10 (UTC 1) Link to this comment
It’s very perfect. It’s helpful to me.
Amiel D.
7 December, 2009 at 12:53 (UTC 1) Link to this comment
Could you please tell me, which method is faster?
I have a similar issue – try to use Tom Kyte’s RunStat scripts but the result did not reveal any notables differences
tia,
Amiel D.
Marco Gralike
7 December, 2009 at 13:42 (UTC 1) Link to this comment
XMLTable is the way to go, due to the fact that this is and will be the only supported function.
XMLSequence, due to its Oracle propriety nature is deprecated from 11.2 and onwards. XMLTable is part of the official SQL/XML,XQuery language and/or all mayor XML supported database systems.
Amiel D.
7 December, 2009 at 14:03 (UTC 1) Link to this comment
do you see any performence diffrence between
XmlTable, with 4 namespaces i.e
XmlTable(namespaces(‘a:ns1=”http://a.b.org/”‘ as “a”,
‘b:ns1=”http://a.b.org/”‘ as “b”)
compare to
nested extract’s?
Marco Gralike
7 February, 2013 at 12:05 (UTC 1) Link to this comment
Extra examples here: https://forums.oracle.com/forums/thread.jspa?threadID=2496029