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

Marco Gralike Written by:

6 Comments

  1. Amos
    January 8

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

  2. Amiel D.
    December 7

    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. December 7

    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.
    December 7

    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
    June 12

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

Comments are closed.