XQuery/XML to SQL

Tabular XML can be exported to SQL by generating the create statement:

 declare function generic:element-to-SQL-create($element) {
  ("create table ", name($element), $generic:nl ,
    
      string-join(
         for $node in $element/*[1]/*
          return 
              concat ("     ",name($node) , " varchar(20)" ),
              concat(',',$generic:nl)
          ),
          ";",$generic:nl
   )
 };
 

and the insert statements:

declare function generic: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)]),'"'),
                  ","
                  ),
          ");",$generic:nl
         )
};

and using these two functions in a script:

declare option exist:serialize  "method=text media-type=text/text";

import module namespace generic = "http://www.cems.uwe.ac.uk/generic" at "../lib/generic.xqm";
let $x := response:set-header('Content-Disposition','inline;filename=emp.sql')
return
   (generic:element-to-SQL-create(/EmpTable),
    generic:element-to-SQL-insert(/EmpTable)
   )

Generate SQL (not yet tested )

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.