I'm trying to get output from SQL Anywhere 12.01 in an XML format to feed into another tool (DHTMLX grid tool) that reads the XML formatted data. It seems that the tool is looking for cellular formatted data where my output has the cell data as elements of the row instead.

This is clearly a function of my lack of understanding of XML in general, but I was hoping for some pointers to make this elemental data readable to something looking for cellular data (hope those are even words).

This is the example they give:

<?xml version="1.0" encoding="UTF-8"?>
<rows>
    <row id="a">
        <cell>Model 1</cell>
        <cell>100</cell>
        <cell>399</cell>
    </row>
    <row id="b">
        <cell>Model 2</cell>
        <cell>50</cell>
        <cell>649</cell>
    </row>
    <row id="c">
        <cell>Model 3</cell>
        <cell>70</cell>
        <cell>499</cell>
    </row>
</rows>

In trying to replicate this I made a table:

CREATE TABLE AAXML (
    "ID" VARCHAR(2) NULL,
    "Model" VARCHAR(30) NULL,
    "Cost" INTEGER NULL,
    "Quantity" INTEGER NULL
    ) ;
INSERT INTO "AAXML" ("ID","Model","Cost","Quantity") VALUES('a','Model 1',100,399);
INSERT INTO "AAXML" ("ID","Model","Cost","Quantity") VALUES('b','Model 2',50,649);
INSERT INTO "AAXML" ("ID","Model","Cost","Quantity") VALUES('c','Model 3',70,499);

When I run this select:

 select * from AAXML for xml raw

I get this output:

  <row ID="a" Model="Model 1" Cost="100" Quantity="399"/>
  <row ID="b" Model="Model 2" Cost="50" Quantity="649"/>
  <row ID="c" Model="Model 3" Cost="70" Quantity="499"/>

(added line breaks for readability)

When I run this select:

select * from AAXML for xml auto

I get this output:

<AAXML ID="a" Model="Model 1" Cost="100" Quantity="399"/>
<AAXML ID="b" Model="Model 2" Cost="50" Quantity="649"/>
<AAXML ID="c" Model="Model 3" Cost="70" Quantity="499"/>

(added line breaks for readability)

asked 18 Jun '13, 11:27

Siger%20Matt's gravatar image

Siger Matt
3.2k496697
accept rate: 13%

edited 18 Jun '13, 11:28

This is a non-answer, as I was thinking adding the ELEMENTS parameter to the FOR XML AUTO would already do the trick - but it doesn't as it does just build a one-level hierarchy of nodes:

select * from AAXML for xml auto, elements

returns

<aaxml>
    <id>a</id>
    <model>Model 1</model>
    <cost>100</cost>
    <quantity>399</quantity>
</aaxml>
<aaxml>
    <id>b</id>
    <model>Model 2</model>
    <cost>50</cost>
    <quantity>649</quantity>
</aaxml>
<aaxml>
    <id>c</id>
    <model>Model 3</model>
    <cost>70</cost>
    <quantity>499</quantity>
</aaxml>
(18 Jun '13, 12:23) Volker Barth

If you don't want to use for xml and have a little more manual control, you can use the various XML builtin functions. This returns the same data as your example (minus the <?xml header):

select xmlelement( 'rows', xmlagg(
    xmlelement( 'row', xmlattributes( id ), xmlconcat(
    xmlelement( 'cell', model ),
    xmlelement( 'cell', cost ),
    xmlelement( 'cell', quantity ) ) ) ) )
from aaxml
permanent link

answered 18 Jun '13, 19:28

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

edited 18 Jun '13, 19:33

I don't believe that SA's XML generation code will be able to generate the format that the tool is expecting...

but you can always roll your own:

select '<rows>'
   || list( string( '<row id="', id, '">'
                    '<cell>', "model", 
                    '</cell><cell>', "cost", 
                    '</cell><cell>', "quantity"
                    '</cell></row>' ), '' )
   || '</rows>
from AAXML;
permanent link

answered 18 Jun '13, 12:30

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

Hm, Mark's response looks way easier, however, here's an attempt with FOR XML EXPLICIT - I used that to specify that ID is returned on a higher level than the other columns:

select xmlelement(NAME rows,
   (select 1 as Tag, NULL as Parent,
      ID as [row!1!id],
      Model as [row!1!Model!element],
      Cost as [row!1!Cost!element],
      Quantity as [row!1!Quantity!element]
   from  
   order by 3, 1
   for xml explicit));

This returns

<rows>
    <row id="a">
        <model>Model 1</model>
        <cost>100</cost>
        <quantity>399</quantity>
    </row>
    <row id="b">
        <model>Model 2</model>
        <cost>50</cost>
        <quantity>649</quantity>
    </row>
    <row id="c">
        <model>Model 3</model>
        <cost>70</cost>
        <quantity>499</quantity>
    </row>
</rows>

As you see, the inner nodes are not generically named "cell" (and I think SA won't allow for that as this would require non-unique column names in the result set), but it's quite near, methinks...

At least it was a nice exercise on my part:)

permanent link

answered 18 Jun '13, 13:03

Volker%20Barth's gravatar image

Volker Barth
30.6k305455662
accept rate: 32%

edited 18 Jun '13, 13:05

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:

×21
×19

question asked: 18 Jun '13, 11:27

question was seen: 2,466 times

last updated: 18 Jun '13, 19:33