Unload table as XML

[Former Member]

Is there any easy way of unloading data from an Sql Anywhere 10 database table as XML?

[VolkerBarth · Accepted solution]

Just a quick and short answer - There are several ways to unload as XML - among others

• the ISQL OUTPUT ... FORMAT XML command
• SELECT with a FOR XML clause

It's well documented in the SA 10 docs: SQL Anywhere® Server - SQL Usage > Using XML in the Database

One easy way to select a whole table might be to use

SELECT * FROM MyTable ORDER BY MyPkColumn
FOR XML AUTO;

[Breck_Carter · Accepted solution]

Don't forget to mention UNLOAD SELECT ... etcetera to get a file.

[justin_willey]

If you want elements rather than attributes then try:

select * from Category for xml auto, elements

[Former Member]

I took a short look at the OUTPUT TO ... FORMAT XML clause. I didn't fund the generated xml very useful. For example, if i have a Categories table with this structure:

CategoryId (PK), Name, Description

I was expecting something like

<root>
<Category>
<CategoryId>1</CategoryId>
<Name>Sports</name>
<Description>All sports items</Description>
</Category>
<Category>
...
</Category>
</root>

The xml generated does look complicated:

<row>
    <column name="CategoryId">...</column>
    <column name="Name">...</column>
    <column name="Description">...</column>
<row>

Anyways, thanks. I'll dig a bit deeper.

[VolkerBarth]

Sergio, then the suggestions with SELECT ... FOR XML AUTO, ELEMENTS should fit, as a quick test with "SELECT * FROM sysdomain FOR XML AUTO, elements;" shows.

In order to get a root, you might have to use wrap your select in a SELECT XMLELEMENT( NAME root, (<yourselecthere> ));