title: Wrapping inner XML nodes with an outer node in TSQL
body: <p>Take the following TSQL</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>Generated from these tables.</p>
  
  <p><strong>Orders</strong></p>
  
  <pre><code>Id   type
  ----------------
  1    Pending
  2    Active
  </code></pre>
  
  <p><strong>Order Parts</strong></p>
  
  <pre><code>id   orderid
  -------------
  1       1
  2       1
  3       3
  4       2
  </code></pre>
  
  <p>It generates this XML</p>
  
  <pre><code>&lt;application&gt;
    &lt;order id="1"&gt;
      &lt;orderpart orderid="1" /&gt;
      &lt;orderpart orderid="2" /&gt;
      &lt;orderpart orderid="3" /&gt;
      &lt;orderpart orderid="4" /&gt;
    &lt;/order&gt;
    &lt;order id="2"&gt;
      &lt;orderpart orderid="1" /&gt;
      &lt;orderpart orderid="2" /&gt;
      &lt;orderpart orderid="3" /&gt;
      &lt;orderpart orderid="4" /&gt;
    &lt;/order&gt;
  &lt;/application&gt;
  </code></pre>
  
  <p>How do I add outer plural tags using TSQL around orders and orderparts so that it looks like the following?  </p>
  
  <pre><code>&lt;application&gt;
    &lt;orders&gt;
      &lt;order id="1"&gt;
        &lt;orderparts&gt;
          &lt;orderpart orderid="1" /&gt;
          &lt;orderpart orderid="2" /&gt;
          &lt;orderpart orderid="3" /&gt;
          &lt;orderpart orderid="4" /&gt;
        &lt;/orderparts&gt;
      &lt;/order&gt;
      &lt;order id="2"&gt;
        &lt;orderparts&gt;
          &lt;orderpart orderid="1" /&gt;
          &lt;orderpart orderid="2" /&gt;
          &lt;orderpart orderid="3" /&gt;
          &lt;orderpart orderid="4" /&gt;
        &lt;/orderparts&gt;
      &lt;/order&gt;
    &lt;/orders&gt;
  &lt;/application&gt;
  </code></pre>
  
  <p>Thanks!</p>