flurk118 (10/25/2007)
Nice article.Has anyone got any examples of similar XML output but with header information included.
Following on from Jacobs article and presuming the existence of an OrderHeader table, the output would look something like attached
Ta
A dirty quick way of doing this is as follows:
SELECT CAST(' ' +
(SELECT
'Jacob' AS 'Name',
'401, TIME SQUARE' AS 'Address',
'007' AS 'Code'
FOR XML PATH('OrderHeader')
) +
(SELECT
OrderNumber,
ItemNumber,
Qty
FROM OrderDetails FOR XML AUTO, ELEMENTS
)+
' ' AS XML)
However, a sweet and gentle way of doing this is by using the EXPLICIT keyword.
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'OrderInfo!1',
NULL AS 'OrderHeader!2!Name!element',
NULL AS 'OrderHeader!2!Address!element',
NULL AS 'OrderHeader!2!Code!element',
NULL AS 'OrderDetails!3!OrderNumber!element',
NULL AS 'OrderDetails!3!ItemNumber!element',
NULL AS 'OrderDetails!3!Qty!element'
UNION
SELECT
2 AS Tag,
1 AS Parent,
NULL,
'Jacob',
'401, Time Square',
'999',
NULL,
NULL,
NULL
UNION
SELECT
3 AS Tag,
1 AS Parent,
NULL,
NULL,
NULL,
NULL,
OrderNumber,
ItemNumber,
Qty
FROM
OrderDetails
FOR XML EXPLICIT
Both of the above queries give the same output that you asked.
.