SQL into XML

Re: 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" <ListGroup@hotm ail.com> wrote in message
news:13d0aab8.0 309190242.1f6ac [email protected] gle.com...[color=blue]
> 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[/color]