Wrapping inner XML nodes with an outer node in TSQL



Take the following TSQL



SELECT
o.id as '@id',
(SELECT
op.id as '@orderid'
FROM
Orders o
INNER JOIN
OrderParts op ON o.id = op.orderId
FOR XML PATH ('orderpart'), TYPE)
FROM
Orders o
FOR XML PATH ('order'), ROOT('application')


Generated from these tables.


Orders



Id type
----------------
1 Pending
2 Active


Order Parts



id orderid
-------------
1 1
2 1
3 3
4 2


It generates this XML



<application>
<order id="1">
<orderpart orderid="1" />
<orderpart orderid="2" />
<orderpart orderid="3" />
<orderpart orderid="4" />
</order>
<order id="2">
<orderpart orderid="1" />
<orderpart orderid="2" />
<orderpart orderid="3" />
<orderpart orderid="4" />
</order>
</application>


How do I add outer plural tags using TSQL around orders and orderparts so that it looks like the following?



<application>
<orders>
<order id="1">
<orderparts>
<orderpart orderid="1" />
<orderpart orderid="2" />
<orderpart orderid="3" />
<orderpart orderid="4" />
</orderparts>
</order>
<order id="2">
<orderparts>
<orderpart orderid="1" />
<orderpart orderid="2" />
<orderpart orderid="3" />
<orderpart orderid="4" />
</orderparts>
</order>
</orders>
</application>


Thanks!


No comments:

Post a Comment