Grouping XLM Output in TSQL

[gbaughma]

I am creating an XML file of orders that include 4 joined tables; the Order Headers, the Customer, the Order Details, and the Payment.

I have been successful in outputting my SQL queries in XML (Thanks, George!), but now I need to group things properly.

In other words, I'm getting a separate XML entry for each line of the order (as is to be expected)

So, in a nutshell, what I'm getting is something like this:

<Order>
<Name>John Smith</Name>
<Address>123 Main Street</address>
.
.
<SKU>123</SKU>
<Description>Something</Description>
<Quantity>2</Quantity>
</Order>
<Order>
<Name>John Smith</Name>
<Address>123 Main Street</address>
.
.
<SKU>456</SKU>
<Description>Item #2</Description>
<Quantity>1</Quantity>
</Order>

... and what I am trying to accomplish is:

<Order>
<Name>John Smith</Name>
<Address>123 Main Street</address>
.
.
<Items>
<SKU>123</SKU>
<Description>Something</Description>
<Quantity>2</Quantity>
<SKU>456</SKU>
<Description>Item #2</Description>
<Quantity>1</Quantity>
</Items>
</Order>

So that all line items for an order are grouped under one "Order" heading.

Any thoughts?

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Never mind... I found a snippet that gives me what I need.

Select 
    (
        Select 
            [Col1] As [Col1],
            [Col2] As [Col2]
        From [dbo].[NestedTable] As T1
        Where T0.[Key] = T1.[Key]
        FOR XML PATH('NestedTable'), TYPE   
    ),
    [Col1] As [Col1],
    [Col2] As [Col2],
From [dbo].[TopLevelTable] As T0
FOR XML PATH('TopLevelTable'), ROOT('TopLevelTableItems')

Works beautifully.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com