[xquery-talk] SQL to XML with XQuery?

[Ghislain Fourny]

Dear Michael,

I got the query below to work on the sample that you gave us. It gives the required input with Zorba and requires XQuery 3.0 (for the windows). It only took a few minutes to write and can probably be improved, but this should give you a starting point.

A word of caution: it should work on a bigger input (with more tables) _under the condition that the patterns in the input SQL stay strictly identical_ (same arrangements of comments on the lines, same whitespaces, spaces and tabs, no other commands than those, and so on. In short: no surprises).

In general, if things do not work and you start spending a lot of time debugging and adapting the transformation each time a new input is tried, this is a sign that a lexer and parser, as Mike suggested, may be a better solution, and that this ad-hoc solution is too ad-hoc for the use case at hand.

I hope it helps.

Kind regards,
Ghislain

______
<database>
{
  let $query := "" (: Put the query here -- I put it into a trivial XML file that I opened with doc()/a :)
  for tumbling window $table in tokenize($query, '\n')
  start $line when starts-with($line, "CREATE TABLE")
  end next $next when starts-with($next, "CREATE TABLE")
  let $title := replace($table[1], "CREATE TABLE ", "")

let $columns :=
    for tumbling window $fields in $table
    start previous $p when starts-with($p, "(")
    end next $n when starts-with($n, ");")
    for $field in $fields
    return <name>{replace($field, " ([A-Za-z0-9]+)\txxx;", "$1")}</name>

let $comments :=
    for tumbling window $fields in $table
    start $s when starts-with($s, "COMMENT")
    end $e when starts-with($e, "COMMENT")
    for $field in $fields
    let $comments := replace($field, "COMMENT ON COLUMN " || $title || "\.([A-Za-z0-9]+) '([A-Za-z0-9]+)';" , "$1 $2")
    return <comment field="{tokenize($comments, ' ')[1]}">{tokenize($comments, " ")[2]}</comment>

return
  <table>
    <title>{$title}</title>
    {
      for $c in $columns
      return <column>
        {
          $c,
          <comment>{$comments[@field eq $c]/string()}</comment>
        }
      </column>
    }
  </table>
}
</database>
______

> On 10 Aug 2017, at 10:36, xquery at docbook-autor.de wrote:
> 
> 
> Hi Christian,
> 
>> Would you like to parse your SQL (or just DDL) expressions in XQuery
>> and execute them in a second step? Do you want to store SQL data in an
>> XML database in a leter step, or do you only want to create a schema
>> representation of your SQL table definitions in XML, resulting from
>> your SQL statements?
> 
> yes, a "simple" SQL parsing should do.
> 
>> The answer is definitely yes. On a logical level, you can do pretty
>> much everything in XQuery, but I assume your use case is much more
>> practical?
> 
> yes, it is. As I mentioned in my previous response I have to transform
> lots of files with 40 to 100 CREATE TABLEs each into DocBook entity files.
> 
> Done by hand it's extremely unsatisfying... especially because I'm
> dealing with transformation of text into text and not some stream or
> crypto data.
> 
> The plan is that first of all I will generate a DocBook documentation
> out of the SQL data. The next step will be that my customer can fill his
> database with all the still missing comments of his database columns out
> of the then completed entity file (XML to SQL transformation).
> 
> Soweit der Plan... ;-)
> 
> Best regards from Aachen
> Michael
> _______________________________________________
> talk at x-query.com
> http://x-query.com/mailman/listinfo/talk