Search notes:

Oracle SQL functions: XMLTable

XMLTable: create a relational table (rows/columns) from an XQuery expression.

The function is likely to occur in the from clause.

select x.*
from   dual d,
       xmltable('/root/elem'
         passing  
         xmltype('
<root attrib="hello world">
  <elem id="one"  >foo</elem>
  <elem id="two"  >bar</elem>
  <elem id="three">baz</elem>
  <xyz>Eggs, why and z</xyz>
</root>')
         columns 
           attrib_id  varchar2(7) path '@id',
           dot        varchar2(7) path '.'  ,
           slash      varchar2(7) path '/'
         ) x;
/*
ATTRIB_ DOT     SLASH
------- ------- -------
one     foo     foo
two     bar     bar
three   baz     baz
*/

Github repository oracle-patterns, path: /SQL/functions/xml/xmltable/select.xmltype.dual.sql

Turning an inline-XML document into a table

select
   t.*
from
   xmlTable(
     '/nums/num'
      passing XMLType (
          '<nums>
             <num><n>1</n><t>one</t></num>
             <num><n>1</n><t>one</t></num>
             <num><n>2</n><t>two</t></num>
             <num><n>3</n><t>three</t></num>
             <num><n>5</n><t>five</t></num>
             <num><n>8</n><t>eight</t></num>
          </nums>
      ')
      columns
         n   number       path  'n',
         t   varchar(10)  path  't'
   ) t;

Generate a sequence of numbers

select
   rownum
from
   xmlTable('1 to 42');

Interestingly, the following statement throws an error:

create table tq84_xt as
select
   column_value val
from
   xmlTable('1 to 42');
--
-- ORA-64464: XML event error
-- ORA-19202: Error occurred in XML processing
-- In line 1 of orastream:
-- LPX-00210: expected '<' instead of '1'

See also generating a given amount of records with the xmlTable function.

SQL Plan Operation

xmlTable creates a the plan operation COLLECTION ITERATOR PICKLER FETCH.

explain plan for
select
   *
from
   xmltable('1 to 42');

select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------------------------------------
-- | Id  | Operation                         | Name                  |
-- -------------------------------------------------------------------
-- |   0 | SELECT STATEMENT                  |                       |
-- |   1 |  COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |
-- -------------------------------------------------------------------

Split comma separated values (CSV) to columns

xmltable can be used to to turn comma separated values into columns.

The values need to be preprocessed so that each value is enclosed in apostrophes.

For example, foo,bar,baz needs to be converted to "foo","bar","baz" which can be achieved with '"' || replace(val, ',', '"') || '"'.

Most basic query:

select * from xmltable('"foo","bar","baz"');
--
-- Result Sequence
-- ---------------
-- foo
-- bar
-- baz

Using trim to get varchar2 values and a column alias val:

select trim(column_value) va from xmltable('"foo","bar","baz"');
-- VAL
-- ---
-- foo
-- bar
-- baz

Using replace to embed values in apostrophes:

select trim(column_value) val from xmltable(( -- Note the double parantheses!
  '"' ||                                      -- With single parantheses, error message is thrown: ORA-02000: missing COLUMNS keyword
  replace('foo,bar,baz', ',', '","') ||
  '"'
));