The use of namespaces within XMLTable or the table(xmlsequence(extract()) constuct

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
m4s0n501
Written by:

6 Comments

  1. Amos
    1/8/2009

    It’s very perfect. It’s helpful to me.

  2. Amiel D.
    12/7/2009

    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.

  3. 12/7/2009

    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.

  4. Amiel D.
    12/7/2009

    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?

  5. Hardik
    6/12/2014

    Thanks a lot.
    Helped in solving a problem which i was debugging from a week or more.
    Hats off.
    cheers!!
    :):)

Comments are closed.