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

asked 27 Nov '09, 15:01

Sergio's gravatar image

Sergio
460151820
accept rate: 0%


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%20Barth's gravatar image

Volker Barth
40.1k361549819
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

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%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

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's gravatar image

Sergio
460151820
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29

question asked: 27 Nov '09, 15:01

question was seen: 3,184 times

last updated: 30 Nov '09, 19:09