Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Transform XML Data into SQL Tables

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,]
XQuery-expression
[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

and/or:

column-name data-type
[BY REF | BY VALUE]
[default-clause]
[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.*
FROM
 clients_xml ,
XMLTABLE(
'for $m in
   $col/client
return
   $m'
 PASSING clients_xml.client AS "col"
 COLUMNS
"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)
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!