How to Transform XML Data into SQL Tables

By Allen G. Taylor

Until recently, when thinking about the relationship between SQL and XML, the emphasis has been on converting SQL table data into XML to make it accessible on the Internet. SQL:2008 addressed the complementary problem of converting XML data into SQL tables so that it can be easily queried using standard SQL statements. The XMLTABLE pseudo-function performs this operation. The syntax for XMLTABLE is:

XMLTABLE ( [namespace-declaration,]
[PASSING argument-list]
COLUMNS XMLtbl-column-definitions

where the argument-list is:

value-expression AS identifier

and XMLtbl-column-definitions is a comma-separated list of column definitions, which may contain:

column-name FOR ORDINALITY


column-name data-type
[PATH XQuery-expression]

Here’s an example of how you might use XMLTABLE to extract data from an XML document into an SQL pseudo-table. A pseudo-table isn’t persistent, but in every other respect, it behaves like a regular SQL table. If you want to make it persistent, you can create a table with a CREATE TABLE statement and then insert the XML data into the newly created table.

SELECT clientphone.*
 clients_xml ,
'for $m in
 PASSING clients_xml.client AS "col"
"ClientName" CHARACTER (30) PATH 'ClientName' ,
"Phone" CHARACTER (13) PATH 'phone'
 ) AS clientphone

When you run this statement, you see the following result:

ClientName      Phone
------------------------------ -------------
Abe Abelson     (714)555-1111
Bill Bailey     (714)555-2222
Chuck Wood      (714)555-3333
(3 rows in clientphone)