XQuery/XML to SQL
Appearance
< XQuery
Tabular XML such as
<books> <book> <title>Introduction to XQuery</title> <description>A beginner's guide to XQuery that covers sequences and FLOWR expressions</description> <type>softcover</type> <sales-count>155</sales-count> <price>19.95</price> </book> <book> <title>Document Transformations with XQuery</title> <description>How to transform complex documents like DocBook, TEI and DITA</description> <type>hardcover</type> <sales-count>105</sales-count> <price>59.95</price> </book><!-- ...more books here.... --> </books>
can be exported to an SQL table by generating the create statement:
declare variable $local:nl := " "; declare function local:element-to-SQL-create($element) { ("create table ", name($element), $local:nl , string-join( for $node in $element/*[1]/* return concat (" ",name($node) , " varchar(20)" ), concat(',',$local:nl) ), ";",$local:nl ) };
and the insert statements:
declare function local:element-to-SQL-insert ($element) { for $row in $element/* return concat ( " insert into table ", name($element), " values (", string-join( for $node in $element/*[1]/* return concat('"',data($row/*[name(.)=name($node)]),'"'), "," ), ");",$local:nl ) };
and using these two functions in a script:
declare option exist:serialize "method=text media-type=text/text"; let $xml := doc("/db/apps/xqbook/data/catalog.xml")/* return (local:element-to-SQL-create($xml), local:element-to-SQL-insert($xml) )
This SQL is very general, with all fields defined as varchar because of the lack of a schema. With a Schema, appropriate datatypes could be defined in SQL.