The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
29.3k287438644
accept rate: 32%

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
6.4k101132197
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:

×18

question asked: 27 Nov '09, 15:01

question was seen: 1,052 times

last updated: 30 Nov '09, 19:09