Oracle XMLTABLE Function

XML that arrives from another system has to end up in tables. XMLTABLE turns an XML document into rows and columns: one XQuery expression selects the nodes that become rows, and each column takes its value from a path relative to that node. It is the XML counterpart of JSON_TABLE, and the modern replacement for the deprecated EXTRACTVALUE and XMLSEQUENCE.

Code for This Guide

The main example is in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

xmltable([xmlnamespaces(...),] 'xquery'
         passing xml_expr
         columns column type path 'xpath'
               | column for ordinality
               [, ...])

The row expression is evaluated against the XML passed in, and every node it returns becomes a row. Column paths are relative to that node: 'type' is a child element, '@tail' an attribute, and '.' the node itself. FOR ORDINALITY numbers the rows.

Turn XML into Rows

Example:

select x.*
from   xmltable('/fleet/aircraft'
         passing xmltype('<fleet>
                   <aircraft tail="A6-NAA"><type>A20N</type><seats>162</seats></aircraft>
                   <aircraft tail="A6-NAH"><type>A359</type><seats>325</seats></aircraft>
                 </fleet>')
         columns seq   for ordinality,
                 tail  varchar2(8) path '@tail',
                 type  varchar2(4) path 'type',
                 seats number      path 'seats') x;

Output:

   SEQ TAIL      TYPE       SEATS
______ _________ _______ ________
     1 A6-NAA    A20N         162
     2 A6-NAH    A359         325

Each aircraft element becomes a row, with its tail attribute, type, and seats as typed columns. The x.* select list shows all of them; in practice you would select, join, or insert them like any table's columns.

Documents with Namespaces

Many real documents declare a namespace. Paths then match only if the namespace is declared in XMLNAMESPACES, here as the default namespace.

Example:

-- a document with a default namespace needs XMLNAMESPACES
select x.*
from   xmltable(xmlnamespaces(default 'urn:nimbus:fares'),
                '/fares/fare'
         passing xmltype('<fares xmlns="urn:nimbus:fares">
                            <fare route="DXB-LHR" cabin="ECONOMY">612.50</fare>
                            <fare route="DXB-LHR" cabin="BUSINESS">2890.00</fare>
                          </fares>')
         columns route varchar2(7) path '@route',
                 cabin varchar2(8) path '@cabin',
                 fare  number      path '.') x;

Output:

ROUTE      CABIN           FARE
__________ ___________ ________
DXB-LHR    ECONOMY        612.5
DXB-LHR    BUSINESS        2890

Without the XMLNAMESPACES clause, '/fares/fare' would match nothing and the query would return no rows, with no error, which is a common source of confusion.

Things to Know

• XMLTABLE in FROM can follow a table with an XMLType column: from invoices i, xmltable('/invoice/line' passing i.doc columns ...) x returns the lines of every invoice.
• A column path that matches nothing gives NULL; one that matches several nodes raises an error unless the column is of type XMLTYPE.
• Insert the result directly: insert into fares select ... from xmltable(...).

Related Guides

• Oracle JSON_TABLE Function
• Oracle XMLELEMENT Function

Conclusion

XMLTABLE shreds XML into rows and typed columns: an XQuery selects the row nodes, and relative paths fill the columns. Declare namespaces with XMLNAMESPACES, number rows with FOR ORDINALITY, and use it in place of the deprecated EXTRACTVALUE and XMLSEQUENCE.