Thursday, December 8, 2011

Oracle XML to TABLE using XMLTABLE

Query:
SELECT seq
, ID
, NAME
FROM XMLTABLE('/xml/emp'
PASSING XMLTYPE('ArpitVenkat')
COLUMNS seq FOR ORDINALITY
, ID VARCHAR2(3) PATH '@id'
, NAME varchar2(10) path 'name'
) AS tbl

Output:
SEQ ID NAME
---------- --- ----------
1 3 Arpit
2 4 Venkat