multi level SQL results into XML

[Damjan]

Hi All;

this is not a specific PHP question, but as I am working in PHP....

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

[Jim Dabell]

Re: multi level SQL results into XML

Damjan wrote:

[snip][color=blue]
> My SQL queries return one, two, sometimes even three levels of XML
> data.[/color]
[snip][color=blue]
> You get the point. I do not want to have 3 queries, but only one that
> returns something like described above.[/color]
[snip]

You want a method of returning a sorted tree from SQL, right? Try this:

<URL:http://simon.incutio.c om/archive/2003/06/19/storingTrees>



--
Jim Dabell

[Damjan]

Re: multi level SQL results into XML

Jim Dabell <jim-usenet@jimdabel l.com> wrote in message news:<IuGdnbIxS J1FO_SiRVn-hQ@giganews.com >...[color=blue]
> Damjan wrote:
>
> [snip][color=green]
> > My SQL queries return one, two, sometimes even three levels of XML
> > data.[/color]
> [snip][color=green]
> > You get the point. I do not want to have 3 queries, but only one that
> > returns something like described above.[/color]
> [snip]
>
> You want a method of returning a sorted tree from SQL, right? Try this:
>
> <URL:http://simon.incutio.c om/archive/2003/06/19/storingTrees>[/color]

That's a cool site, and I've read already some about that, but
unfortunately that is not what I need.

THanks anyway

Damjan