Wrapping inner XML nodes with an outer node in TSQL

Question

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!

Answer 1

SELECT 
(
     SELECT o.id AS '@id',
     (
          SELECT op.id as '@orderid'
          FROM OrderParts op 
          WHERE op.orderID = o.ID
          FOR XML PATH ('orderpart'), TYPE
     ) AS 'OrderParts'
     FROM Orders o
     FOR XML PATH ('order'),TYPE
 )
 FOR XML PATH ('orders'),ROOT('application')