Best way to create XML output of SQL Anywhere (v11)?

[vlad1]

Hi, SQL Anywhere 11.0.1.2878

We are in the process of creating an interface for a 3rd party system where we need to export an XML file for each new (or modified) record of a particular table. We are hoping to do this by placing a trigger on the table for insert or update actions, which calls the same stored procedure which outputs 6-7 fields of the new/modified row to XML file.

The stored procedure right now, runs a big string (the 'UNLOAD SELECT...' query) with EXECUTE IMMEDIATE. However, when we tried to end the query with "FORMAT XML", the problem we are facing is that our XML file must look like:

<?xml version="1.0" encoding="UTF-16"?>

<Root>

  <RecordName>

    <Field1>Blablabla</Field1>

    <Field2>Blablabla</Field2>

  </RecordName>

...

</Root>

and the "Output to C:\test\test.xml FORMAT XML" did not include the first <?xml version="1.0"...> definition line, and neither the <Root> tags.

We have then tried using the XMLGEN function, meaning that our SELECT query which unloads the results looks like:

SELECT XMLGEN( '<?xml version="1.0" encoding="UTF-16" ?>  <Root>{$x}</Root>',

                (SELECT

Table.Field1 as Field1,

Table.Field2 as Field2,

...

for xml auto, elements) AS x );

This worked well in outputting the <?xml version="1.0" encoding="UTF-16" ?> line at the top of the file, and has put in the <Root> tags as well, however the file exported is an ascii file, and not in true UTF encoding. This is maybe not important as we would probably not work with characters which require UTF, but it would be better to get the UTF encoding. 

What would be the best approach to get the UTF-16 (or UTF-8) XML file output out of a stored procedure, that looks like the example above? 

Thanks

[former_member182948 · Accepted solution]

Hi,

Probably the ENCODING clause with the UNLOAD statement will be useful.

eg.

UNLOAD
    SELECT XMLGEN( '<?xml version="1.0" encoding="UTF-16" ?>  <Root>{$x}</Root>',
        (SELECT
                Table.Field1 as Field1,
                Table.Field2 as Field2,
...
    for xml auto, elements) AS x )
INTO FILE filename ENCODING 'UTF-16' FORMAT TEXT QUOTE '';

At this time, You have to be careful about quote marks.

You can use the QUOTE clause to remove it.

For details, please see the following document.

"UNLOAD statement"

http://dcx.sap.com/index.html#1101/en/dbreference_en11/unload-statement.html

Thanks

[vlad1 · Accepted solution]

Oh I feel a bit silly now, I was looking through that exact documentation and somehow I have completely missed the ENCODING option. Duuuuh.

Thanks for the tip, works like a charm.

However, since I've opened the thread, are there any other alternatives for XML output straight from the database? Using the EXECUTE IMMEDIATE and then the UNLOAD SELECT... seems a bit inelegant. Are there any more SQL Anywhere functions that will export data to XML?

[former_member207653 · Accepted solution]

http://dcx.sap.com/index.html#1101/en/dbusage_en11/xmldraftchapter-s-4116817.html

What about the xp_write_file system procedure instead of the UNLOAD statement?

[vlad1 · Accepted solution]

Interesting! Will check it out, thanks!