How to Implement a Database in MySQL for HTML5 and CSS3 Programming
How to Use SQL with Microsoft Access
How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming

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
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Use Comparison Predicates and BETWEEN in SQL Statements
How to Use GROUP BY, HAVING, and ORDER BY SQL Clauses
How to Use SQL:2011 Times and Periods
PHP & MySQL Web Development AIO For Dummies Cheat Sheet
Advertisement

Inside Dummies.com