SELECT extractvalue(value(d),'/TABLE_NAME') as TABLE_NAMEThe query above extracts the text node under the TABLE_NAME element in an XML file containing the canonical representation of a table, as it would exported from Apex invoking the export table utility.
FROM dual e,
dbms_xmlgen.getxml('select * from user_tables')),
In order to simulate the output of Apex, i used packaged function DBMS_XMLGEN.GETXML.
The key components of the query are highlighted in bold typeface.
Function extract returns a set of nodes that is, in other words, an XML fragment.
An XPath expression identifies the desired nodes (/ROW/ROWSET/TABLE_NAME).
Function xmlsequence takes the node-set and converts it into a varray of XMLtype.
Once i have joined the source table with the nested array of nodes, i can finally apply function extractvalue that converts nodes into scalar values, that is simple oracle types.
Note also that i had to explicitly convert the document from CLOB type into XMLType.
Updated January 12, 2008.
i forgot to explain why i put DUAL table in my example: here DUAL represents the outer table that will be probably present in the real life. For instance, here is a similar statement i've created today for extracting translations from an XML file (in XLIFF format), that is stored in a staging table, that i called XML_IMPORTS:
select extractvalue(value(d),'/trans-unit/source') as source,
extractvalue(value(d),'/trans-unit/target') as target
from xml_imports x,
table(xmlsequence(extract(x.xmlfile, '/xliff/file/body/trans-unit'))) d
where x.id = 31