XQuery/XML 到 SQL

表格 XML 例如

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

可以通过生成创建语句导出到 SQL 表中

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

以及插入语句

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

并在脚本中使用这两个函数

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

生成 SQL

此 SQL 非常通用，由于缺乏架构，所有字段均定义为 varchar。有了架构，可以在 SQL 中定义适当的数据类型。