[xquery-talk] SQL to XML with XQuery?

[x***@docbook-autor.de]

Hi,

I know that XQuery is typically used for transforming XML into other
text file formats.

But is it possible to use XQuery for the other way round?

I want to transform a very simple SQL Create Table statement into XML.


SQL
===

CREATE TABLE mytable1

(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);

COMMENT ON COLUMN mytable1.FIELD1 'Description1';
COMMENT ON COLUMN mytable1.FIELD2 'Description2';

CREATE TABLE mytable2

(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);

COMMENT ON COLUMN mytable2.FIELD1 'Description1';
COMMENT ON COLUMN mytable2.FIELD3 'Description3';


XML
===

<table>
<title>mytable1</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment>Description2</comment>
</column>
<column>
<name>FIELD1</name>
<comment></comment>
</column>
</table>

<table>
<title>mytable2</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment></comment>
</column>
<column>
<name>FIELD1</name>
<comment>Description3</comment>
</column>
</table>

Can this be done via XQuery? If not which tool could possibly fit my needs?

Best regards
Michael
_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

[Ihe Onwuka]

DB2 version 10 onwards lets you mix SQL and XQuery commands in one query.

Post by x***@docbook-autor.de
Hi,
I know that XQuery is typically used for transforming XML into other
text file formats.
But is it possible to use XQuery for the other way round?
I want to transform a very simple SQL Create Table statement into XML.
SQL
===
CREATE TABLE mytable1
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable1.FIELD1 'Description1';
COMMENT ON COLUMN mytable1.FIELD2 'Description2';
CREATE TABLE mytable2
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable2.FIELD1 'Description1';
COMMENT ON COLUMN mytable2.FIELD3 'Description3';
XML
===
<table>
<title>mytable1</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment>Description2</comment>
</column>
<column>
<name>FIELD1</name>
<comment></comment>
</column>
</table>
<table>
<title>mytable2</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment></comment>
</column>
<column>
<name>FIELD1</name>
<comment>Description3</comment>
</column>
</table>
Can this be done via XQuery? If not which tool could possibly fit my needs?
Best regards
Michael
_______________________________________________
http://x-query.com/mailman/listinfo/talk

[Ihe Onwuka]

Here.

https://www.ibm.com/developerworks/data/library/techarticle/dm-1006queriespurexml/index.html

Post by Ihe Onwuka
DB2 version 10 onwards lets you mix SQL and XQuery commands in one query.

Post by x***@docbook-autor.de
Hi,
I know that XQuery is typically used for transforming XML into other
text file formats.
But is it possible to use XQuery for the other way round?
I want to transform a very simple SQL Create Table statement into XML.
SQL
===
CREATE TABLE mytable1
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable1.FIELD1 'Description1';
COMMENT ON COLUMN mytable1.FIELD2 'Description2';
CREATE TABLE mytable2
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable2.FIELD1 'Description1';
COMMENT ON COLUMN mytable2.FIELD3 'Description3';
XML
===
<table>
<title>mytable1</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment>Description2</comment>
</column>
<column>
<name>FIELD1</name>
<comment></comment>
</column>
</table>
<table>
<title>mytable2</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment></comment>
</column>
<column>
<name>FIELD1</name>
<comment>Description3</comment>
</column>
</table>
Can this be done via XQuery? If not which tool could possibly fit my needs?
Best regards
Michael
_______________________________________________
http://x-query.com/mailman/listinfo/talk

[Michael Kay]

XQuery has no built-in capability to parse SQL or to execute the result after parsing.

You could generate an XQuery parser for a subset of SQL using REx (http://www.bottlecaps.de/rex/), which would give you an XML representation of the SQL command, and you could then write an XSLT or XQuery program that transforms this into your desired output.

Michael Kay
Saxonica

Post by x***@docbook-autor.de
Hi,
I know that XQuery is typically used for transforming XML into other
text file formats.
But is it possible to use XQuery for the other way round?
I want to transform a very simple SQL Create Table statement into XML.
SQL
===
CREATE TABLE mytable1
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable1.FIELD1 'Description1';
COMMENT ON COLUMN mytable1.FIELD2 'Description2';
CREATE TABLE mytable2
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable2.FIELD1 'Description1';
COMMENT ON COLUMN mytable2.FIELD3 'Description3';
XML
===
<table>
<title>mytable1</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment>Description2</comment>
</column>
<column>
<name>FIELD1</name>
<comment></comment>
</column>
</table>
<table>
<title>mytable2</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment></comment>
</column>
<column>
<name>FIELD1</name>
<comment>Description3</comment>
</column>
</table>
Can this be done via XQuery? If not which tool could possibly fit my needs?
Best regards
Michael
_______________________________________________
http://x-query.com/mailman/listinfo/talk

_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

[Ghislain Fourny]

Hi Michael,

I agree with Mike, but would add that with XQuery's FLWOR expressions, window clauses, builtin functions (tokenize, replace, match, etc) as well as EXPath modules, you can achieve a lot on text input that is "not too unstructured". I did it quite a few times. Some engines also have builtin modules to parse CSV and others.

Of course, parsing a full language like SQL is best done using the typical approaches (lexer, grammar, etc) as Mike suggests, and is not trivial. But if the subset is really very simple (as simple as your example), known in advance, and if there are no irregularities in newlines, etc, then the above, more ad-hoc approach could work as well quite straightforwardly: a for to iterate on the lines, start tumbling windows at rows that start with "CREATE TABLE", then sub-windows to catch the parentheses and the COMMENTs, and then convert the contents to XML nodes.

If it gets more complex, though, for example if the commands spread differently over lines, or if you cannot have any guarantees on the input (i.e., all you know is that it will be SQL), then generating a parser with a library will definitely be a very much worthier investment.

You would probably walk for a 500-yard trip, and you would probably take a plane, arriving 2 hours in advance at the airport, for a 1000-mile trip. It all depends on what you want to achieve.

I hope this helps.

Kind regards,
Ghislain

Post by Michael Kay
XQuery has no built-in capability to parse SQL or to execute the result after parsing.
You could generate an XQuery parser for a subset of SQL using REx (http://www.bottlecaps.de/rex/), which would give you an XML representation of the SQL command, and you could then write an XSLT or XQuery program that transforms this into your desired output.
Michael Kay
Saxonica

Post by x***@docbook-autor.de
Hi,
I know that XQuery is typically used for transforming XML into other
text file formats.
But is it possible to use XQuery for the other way round?
I want to transform a very simple SQL Create Table statement into XML.
SQL
===
CREATE TABLE mytable1
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable1.FIELD1 'Description1';
COMMENT ON COLUMN mytable1.FIELD2 'Description2';
CREATE TABLE mytable2
(
FIELD1 xxx;
FIELD2 xxx;
FIELD3 xxx;
);
COMMENT ON COLUMN mytable2.FIELD1 'Description1';
COMMENT ON COLUMN mytable2.FIELD3 'Description3';
XML
===
<table>
<title>mytable1</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment>Description2</comment>
</column>
<column>
<name>FIELD1</name>
<comment></comment>
</column>
</table>
<table>
<title>mytable2</title>
<column>
<name>FIELD1</name>
<comment>Description1</comment>
</column>
<column>
<name>FIELD2</name>
<comment></comment>
</column>
<column>
<name>FIELD1</name>
<comment>Description3</comment>
</column>
</table>
Can this be done via XQuery? If not which tool could possibly fit my needs?
Best regards
Michael
_______________________________________________
http://x-query.com/mailman/listinfo/talk

_______________________________________________
http://x-query.com/mailman/listinfo/talk

_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

[x***@docbook-autor.de]

Hi Mike and Ghislain,

Post by Ghislain Fourny
Of course, parsing a full language like SQL is best done using the typical approaches (lexer, grammar, etc) as Mike suggests, and is not trivial. But if the subset is really very simple (as simple as your example), known in advance, and if there are no irregularities in newlines, etc, then the above, more ad-hoc approach could work as well quite straightforwardly: a for to iterate on the lines, start tumbling windows at rows that start with "CREATE TABLE", then sub-windows to catch the parentheses and the COMMENTs, and then convert the contents to XML nodes.

yes, the scenario is really that simple.

I get files with round about 40 to 100 CREATE TABLEs each and have to
transform those into XML files (subsequently I have to transform those
XML files into DocBook entity files where XQuery would come into play
anyway).

I tried to import those CREATE TABLEs into MySQL and to export the
resulting database as XML. Unfortunately MySQL only exports to <table
name="mytable1"> tag level with the CREATE TABLE state as value:

<pma:table name="mytable1">
CREATE TABLE `mytable1` (
`FIELD1` xxx DEFAULT NULL,
`FIELD2` xxx DEFAULT NULL,
`FIELD3` xxx DEFAULT NULL
)
</pma:table>

Maybe I'm missing something with the XML export feature of MySQL.

I'm not an XQuery expert... maybe that export will do already to use
XQuery for generating the DocBook entity file.

Is it possible to take apart tag values with XQuery so that every
'FIELDx' gets its own entry after the transformation?

@Ihe Onwuka: Using DB2 is not an option. I just get those files. Best i
can do is to use MySQL.

Best regards
Michael
_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

[Christian Grün]

Hi Michael (Sahm),

Post by x***@docbook-autor.de
I want to transform a very simple SQL Create Table statement into XML.

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?

Post by x***@docbook-autor.de
Can this be done via XQuery?

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?

Best,
Christian

_______________________________________________
***@x-query.com
htt

[x***@docbook-autor.de]

Hi Christian,

Post by Christian Grün
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.

Post by Christian Grün
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
_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

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

Post by x***@docbook-autor.de
Hi Christian,

Post by Christian Grün
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.

Post by Christian Grün
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
_______________________________________________
http://x-query.com/mailman/listinfo/talk

_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk

[x***@docbook-autor.de]

Hi Ghislain,

Post by Ghislain Fourny
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.

thanks a lot! I will test it over the weekend and report the results.

Have a nice weekend and best regards
Michael
_______________________________________________
***@x-query.com
http://x-query.com/mailman/listinfo/talk