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  '                                                
  4     true                                                  
  5     a                                                         
  6         
  7        SFO                                                    
  8        limit1                                     
  9                                                                          
 10         
 11        boston                                                 
 12        limit2                                   
 13                                                                          
 14  '                                                                           
 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  '                                                     
  4     true                                                       
  5     a                                                              
  6              
  7        SFO                                                         
  8        limit1                                          
  9                                                                               
 10              
 11        boston                                                      
 12        limit2                                        
 13                                                                               
 14  '
 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