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