The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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
30.9k309457667
accept rate: 33%

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.8k110144212
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:

×19

question asked: 27 Nov '09, 15:01

question was seen: 1,179 times

last updated: 30 Nov '09, 19:09