XQuery/XML to SQL

Tabular XML such as

Introduction to XQuery A beginner's guide to XQuery that covers sequences and FLOWR expressions softcover 155 19.95        Document Transformations with XQuery How to transform complex documents like DocBook, TEI and DITA hardcover 105 59.95

can be exported to an SQL table by generating the create statement:

declare variable $local:nl := "&#10;";

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)   )

Generate SQL

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.