Standard for dumping SQL results to XML?

[Marcel Ruff]

Hi,

i need to dump JDBC ResultSets to XML and
send the XML string as a message to different
interested data sinks.

Is there any standard how to format the XML?

For the XML schema following issues come to my mind:

o Easy query with XPath
o Possibility to send binary data (base64, CDATA?)
o 1:n relationship or flat representation?
o ColNames==tagNames versus key/value attributes
o Using a standardized schema


1. Here is a key/value generic approach:

-------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<sql> <!-- Result of a query -->

<desc> <!-- the desc header is optional -->
<statement>select ID, NAME, AGE from car</statement>
<numcolumns>3</numcolumns>
<columnnames>
<column>ID</column>
<column>NAME</column>
<column type='int'>AGE</column>
</columnnames>
<numrows>2</numrows>
</desc>

<row num="0">
<col id='ID'>23345</col>
<col id='NAME'>Fiat</col>
<col id='AGE'>12</col>
</row>
<row num="1">
<col id='ID'>245</col>
<col id='NAME' encoding='base64'>OKFKAL==</col>
<col id='AGE'>24</col>
</row>

</sql>
--------------------------------------

-> Parsing the DOM with XPath is simple: "/sql/row[@num='0']/col[@id='NAME']/text()" ...
-> Parsing with standard string functions is easy (no xml parser)
-> Generic: The schema doesn't change for any data queried
-> parent->child relations are flat
-> The id='NAME' attributes are redundant (see <desc>) and could be omitted


2. Here is a colName==tagName approach:

--------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<sql>
<row num="0">
<NAME>Fiat</NAME>
<AGE>12</AGE>
</row>
<row num="1">
<NAME>Jeep</NAME>
<AGE>2</AGE>
</row>
<row num="2">
<NAME>Audi</NAME>
<AGE>1</AGE>
</row>
</sql>
--------------------------------------

-> Parsing the DOM with XPath is simple: "/sql/row[@num='0']/NAME/text()" ...
-> The schema changes for any data queried!
-> parent->child relations are flat


Any links about this issue and comments are welcome,

regards,

Marcel