SAP SQL Anywhere

Question

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

Accepted Answer

5

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;

permanent link

answered 27 Nov '09, 15:22

Volker Barth
39.7k●358●546●815
accept rate: 34%

edited 27 Nov '09, 16:29

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

(30 Nov '09, 15:01) Breck Carter

flat view

Answer 2

1	If you want elements rather than attributes then try: `select * from Category for xml auto, elements` permanent link answered 30 Nov '09, 19:09 Justin Willey 7.6k●137●179●248 accept rate: 19% flat view

Answer 3

0

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.

permanent link

answered 30 Nov '09, 09:52

Sergio
460●15●18●20
accept rate: 0%

edited 30 Nov '09, 10:03

1

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> ));

(02 Dec '09, 09:09) Volker Barth

flat view

SAP SQL Anywhere

Unload table as XML

Follow this question

Related questions