XML Formatting

[former_SQLA_member1694868]

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)

[VolkerBarth]

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>

[graeme_perrow · Accepted solution]

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

[MarkCulp]

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;

[VolkerBarth]

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