Master/Detail Query - Got confused

Hallo all,

I’ve two tables (Order_Headers and Order_Details) in SQL Server 2005:

Order_Headers

DocType Order#SubTotalVATTotal

01110018118

01220036236

Order_Details

Order#Line# QTY SKUUnit_Price

111A30

121B70

211C40

221D100

231E60

I need to query both tables and return Master/Detail XML with the following format, ie. Need to return all order lines for a particular order header#. I’m using FOR XML PATH('Document'), ROOT('XML_QueryDocument') but I’m not able to add the “<LineItem>” tag for each order line.

<XML_QueryDocument>

<Document>

<DocType>01</DocumentType>

<OrderNumber>1</OrderNumber>

<SubTotal>100</SubTotal>

<VAT>18</VAT>

<Total>118</Total>

<LineItem>

<LineNumber>1</LineNumber>

<QTY>1</QTY>

<SKU>A</SKU>

<Unit_Price>30</Unit_Price>

</LineItem>

<LineItem>

<LineNumber>2</LineNumber>

<QTY>1</QTY>

<SKU>B</SKU>

<Unit_Price>70</Unit_Price>

</LineItem>

</Document>

</XML_QueryDocument>

Could anyone please shed some light on how to accomplish this? I think I need several SELECT statements but I got confused.

Thanks in advance,

ST

Code Snippet

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)

CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118

INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'A', 30

INSERT INTO #Order_Detail SELECT 2, 1, 1, 'B', 70

INSERT INTO #Order_Detail SELECT 1, 1, 1, 'C', 40

INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100

INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

SELECT * FROM #Order_Headers oh

FULL OUTER JOIN #Order_Detail od on oh.OrderNum = od.OrderNum

DROP TABLE #Order_Headers

DROP TABLE #Order_Detail

Adamus

|||

Thanks Adamus for your prompt reply!

If I add FOR XML PATH('Document'), ROOT('XML_QueryDocument') at the end of the SELECT statement, I get the following.

It's repeating tags DocType, OrderNum, SubTotal for each order line. This is exactly what I wanted left out. I think the query needs to create a LineItem tag for each order line and show the order header just once either at the top or at the bottom inside a <document> tag. This is where I got confused.

Thanks,

ST

<XML_QueryDocument>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</Document>
<Document>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<OrderNum>1</OrderNum>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>C </SKU>
<UnitPrice>40</UnitPrice>
</Document>

.......

|||

Perhaps something like this:

Code Snippet

SELECT
Orders.DocType,
Orders.OrderNum,
Orders.SubTotal,
Orders.VAT,
Orders.Total,
LineItem.LineNum,
LineItem.QTY,
LineItem.SKU,
LineItem.UnitPrice
FROM #Order_Headers Orders
JOIN #Order_Detail LineItem
ON Orders.OrderNum = LineItem.OrderNum
FOR XML AUTO, ELEMENTS

XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<Orders>
<DocType>1</DocType>
<OrderNum>1</OrderNum>
<SubTotal>100</SubTotal>
<VAT>18</VAT>
<Total>118</Total>
<LineItem>
<LineNum>1</LineNum>
<QTY>1</QTY>
<SKU>A </SKU>
<UnitPrice>30</UnitPrice>
</LineItem>
</Orders>
{etc.}

|||

Thanks Arnie also for a good insight.

I modified the code a little bit as the code above doesn't produce well-formed XML (it shows "XML document cannot contain multiple root level elements"). I've also changed the INSERT on #Order_Detail for order# 1 to have 3 lines

Since I only need to return a document at the time I just added a WHERE clause and that's exactly what I need.

Here's the code just in case someone else is after this also:

CREATE TABLE #Order_Headers(DocType int, OrderNum int, SubTotal int, VAT int, Total int)
CREATE TABLE #Order_Detail(OrderNum int, LineNum int, QTY int, SKU char(10), UnitPrice int)

INSERT INTO #Order_Headers SELECT 1, 1, 100, 18, 118
INSERT INTO #Order_Headers SELECT 1, 2, 200, 36, 236

INSERT INTO #Order_Detail SELECT 1, 1, 10, 'A', 30
INSERT INTO #Order_Detail SELECT 1, 2, 20, 'B', 700
INSERT INTO #Order_Detail SELECT 1, 3, 30, 'C', 4000
INSERT INTO #Order_Detail SELECT 3, 1, 1, 'D', 100
INSERT INTO #Order_Detail SELECT 4, 1, 1, 'E', 60

SELECT
Orders.DocType,
Orders.OrderNum,
Orders.SubTotal,
Orders.VAT,
Orders.Total,
LineItem.LineNum,
LineItem.QTY,
LineItem.SKU,
LineItem.UnitPrice
FROM #Order_Headers Orders
JOIN #Order_Detail LineItem
ON Orders.OrderNum = LineItem.OrderNum
WHERE Orders.OrderNum = 1
FOR XML AUTO, ELEMENTS

DROP TABLE #Order_Headers
DROP TABLE #Order_Detail