Tuesday, October 09, 2007

Converting XML leaf nodes into relational data

If you, like me, tend to forget how to build certain non-trivial SQL statements involving XML functions, you won't regret if i post here a sort of memorandum on how to extract data from the leaf nodes of an XML document.

SELECT extractvalue(value(d),'/TABLE_NAME') as TABLE_NAME
FROM dual e,
dbms_xmlgen.getxml('select * from user_tables')),
) d;
The 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.

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


Doug Forbes said...

No I didn't find the solution to my problem because you won't provide the neccassery information. Could you at least provide a DESC of XML_IMPORT so that we know when you are referring to a column in that table.

Anonymous said...

No I didn't find the answer because you examples are missing infomation. COuld you at least provide a DESC for XML_IMPORT.

Byte64 said...

Hi Doug,
the XML_IMPORT table in the given statement must certainly at least two columns, one called ID and another one called XMLFILE. The columns are referenced by the x.column_name notation, so everything that is not preceded by the alias x won't matter.

The examples i gave were simple reminders in case i need to clone a similar statement and i don't remember the syntax.

Let me know if you need some help with a specific problem, i can try to give a hand.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles