SQL into XML

java-programmer, SQL into XML Hi All;

My SQL queries return one, two, sometimes even three levels of XML
data.
So for orders in my system I would like to see in my XML:

<RESULTS>
<order>
<number>3213</number>
<date>Feb 03, 2003</date>
<product>
<name>NEC Monitor</name>
<quantity>3</quantity>
<serial_numbers>
<sn>12321312321</sn>
<sn>44314133422</sn>
<sn>43434343553</sn>
<serial_numbers>
</product>
<product>
<name>Genius Mouse</name>
<quantity>2</quantity>
<serial_numbers>
<sn>23232</sn>
<sn>44343</sn>
<serial_numbers>
</product>
</order>
<order>
<number>444</number>
<date>Mar 06, 2003</date>
<product>
<name>MS Keyboard</name>
<quantity>1</quantity>
<serial_numbers>
<sn>333333</sn>
<serial_numbers>
</product>
</order>
........
</RESULTS>

obviously my query would reutrn

order_num order_date product_name quantity
serial_number
------------------------------------------------------------------------
3231 Feb 03, 2003 NEC Monitor 3 12321312321
3231 Feb 03, 2003 NEC Monitor 3 44314133422
3231 Feb 03, 2003 NEC Monitor 3 43434343553
3231 Feb 03, 2003 Genius Mouse 2 23232
3231 Feb 03, 2003 Genius Mouse 2 44343
444 Mar 06, 2003 MS Keyboard 1 333333

You get the point. I do not want to have 3 queries, but only one that
returns something like described above.

For now I have a functioin that does this, but the algorithm is not
the best, and it only works for two levels (so XML without the s/n for
example).
This must be a common problem, I would appriciate input as to how this
is commonly done, any pointers to web sites, articles... are all
welcome. I'd try to search the groups but for what?:) I would also
like to describe my current algorithm, but for now it seems I would
just bore you.

Looking for some input,

Damjan

java-programmer >> SQL into XML You'll want to take a look at Oracle's XML toolkit. In 8i, there was a
limitation with the number of levels. You couldn't go lower than two. In
9i, that's supposed to be fixed although I've never used it in this version.
The key is to create an object relational view around your tables. I'm
assuming you've got a normalized model. Something like an ORDER table, a
PRODUCT table, and a SERIAL_NO table. You'd create objects for PRODUCT and
SERIAL_NO and then use the "CAST-MULTISET " syntax to define the 1 -> Many
relationships. Once this is done, you can write a single SQL statement that
will represent all of your data. If you pass this to a method in the
OracleXML stuff, it will output the XML in the right format. Check
http://technet.oracle.com for more details.

Byron
"Damjan" <email***@***.com> wrote in message
news:email***@***.com...
> Hi All;
>
> My SQL queries return one, two, sometimes even three levels of XML
> data.
> So for orders in my system I would like to see in my XML:
>
> <RESULTS>
> <order>
> <number>3213</number>
> <date>Feb 03, 2003</date>
> <product>
> <name>NEC Monitor</name>
> <quantity>3</quantity>
> <serial_numbers>
> <sn>12321312321</sn>
> <sn>44314133422</sn>
> <sn>43434343553</sn>
> <serial_numbers>
> </product>
> <product>
> <name>Genius Mouse</name>
> <quantity>2</quantity>
> <serial_numbers>
> <sn>23232</sn>
> <sn>44343</sn>
> <serial_numbers>
> </product>
> </order>
> <order>
> <number>444</number>
> <date>Mar 06, 2003</date>
> <product>
> <name>MS Keyboard</name>
> <quantity>1</quantity>
> <serial_numbers>
> <sn>333333</sn>
> <serial_numbers>
> </product>
> </order>
> ........
> </RESULTS>
>
> obviously my query would reutrn
>
> order_num order_date product_name quantity
> serial_number
> ------------------------------------------------------------------------
> 3231 Feb 03, 2003 NEC Monitor 3 12321312321
> 3231 Feb 03, 2003 NEC Monitor 3 44314133422
> 3231 Feb 03, 2003 NEC Monitor 3 43434343553
> 3231 Feb 03, 2003 Genius Mouse 2 23232
> 3231 Feb 03, 2003 Genius Mouse 2 44343
> 444 Mar 06, 2003 MS Keyboard 1 333333
>
> You get the point. I do not want to have 3 queries, but only one that
> returns something like described above.
>
> For now I have a functioin that does this, but the algorithm is not
> the best, and it only works for two levels (so XML without the s/n for
> example).
> This must be a common problem, I would appriciate input as to how this
> is commonly done, any pointers to web sites, articles... are all
> welcome. I'd try to search the groups but for what?:) I would also
> like to describe my current algorithm, but for now it seems I would
> just bore you.
>
> Looking for some input,
>
> Damjan

java-programmer >> SQL into XML Damjan,

maybe it is me, but I don't understand what your post is about.

Does your post really contain all the information required for an
outsider to understand what you are talking about?

With best regards

Clemens Anhuth

java-programmer >> SQL into XML You make a good point. I re-read the original message and realized that I
assumed he was using Oracle (occupational hazard). A more generic solution
would be to use something like JDOM. I could be wrong, but I remember
hearing about some contributed code (for the JDOM project) that would
connect to a database and turn the resultset into XML. In any event, it
would be helpful to know which database, what version, etc. are being used.
I understand what the end goal is perhaps because I've done the same thing a
couple of years ago.

Byron Lee

"Clemens Anhuth" <email***@***.com> wrote in message
news:bkl21v$2tlds$email***@***.com...
> Damjan,
>
> maybe it is me, but I don't understand what your post is about.
>
> Does your post really contain all the information required for an
> outsider to understand what you are talking about?
>
>
> With best regards
>
> Clemens Anhuth
>
>

Posted: 2003-10-17 1:08:00

java-programmer >> SQL into XML On Mon, 22 Sep 2003 00:52:09 GMT, Byron Lee <email***@***.com> posted this:
>
> ...snip ...
> connect to a database and turn the resultset into XML. In any event, it
perhaps http://castor.exolab.org/
> ...snip ...
>
> Byron Lee
>
--

Mark
listscribbler_at_earthlink.net