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)