XQuery/XML to SQL

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

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.