[xquery-talk] SQL to XML with XQuery?

[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


> On 10 Aug 2017, at 09:24, Michael Kay <mike at saxonica.com> wrote:
> 
> 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
> 
>> On 10 Aug 2017, at 07:07, xquery at docbook-autor.de wrote:
>> 
>> 
>> 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
>> _______________________________________________
>> talk at x-query.com
>> http://x-query.com/mailman/listinfo/talk
> 
> 
> _______________________________________________
> talk at x-query.com
> http://x-query.com/mailman/listinfo/talk