title: SQL Server : FOR XML PATH - nesting / grouping
body: <p>I have data that looks like:</p>
  
  <pre><code>OrderID CustomerID  ItemID  ItemName
  10000   1234        111111  Product A
  10000   1234        222222  Product B
  10000   1234        333333  Product C
  20000   5678        111111  Product A
  20000   5678        222222  Product B
  20000   5678        333333  Product C
  </code></pre>
  
  <p>I want to write a T-SQL query in SQL Server to return the data like this:</p>
  
  <pre><code>&lt;Root&gt;
    &lt;Order&gt;
      &lt;OrderID&gt;10000&lt;/OrderID&gt;
      &lt;CustomerID&gt;1234&lt;/CustomerID&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;11111&lt;/ItemId&gt;
        &lt;ItemName&gt;Product A&lt;/ItemName&gt;
      &lt;/LineItem&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;22222&lt;/ItemId&gt;
        &lt;ItemName&gt;Product B&lt;/ItemName&gt;
      &lt;/LineItem&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;33333&lt;/ItemId&gt;
        &lt;ItemName&gt;Product B&lt;/ItemName&gt;
      &lt;/LineItem&gt;
    &lt;/Order&gt;
    &lt;Order&gt;
      &lt;OrderID&gt;20000&lt;/OrderID&gt;
      &lt;CustomerID&gt;5678&lt;/CustomerID&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;11111&lt;/ItemId&gt;
        &lt;ItemName&gt;Product A&lt;/ItemName&gt;
      &lt;/LineItem&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;22222&lt;/ItemId&gt;
        &lt;ItemName&gt;Product B&lt;/ItemName&gt;
      &lt;/LineItem&gt;
      &lt;LineItem&gt;
        &lt;ItemID&gt;33333&lt;/ItemId&gt;
        &lt;ItemName&gt;Product B&lt;/ItemName&gt;
      &lt;/LineItem&gt;
    &lt;/Order&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>I've tried returning the query in XML using:</p>
  
  <pre><code>FOR XML PATH ('Order'), root ('Root')
  </code></pre>
  
  <p>But that gives me an <code>Order</code> node for each row (6 in total) vs. just an order node for each <code>orderId</code> (2 in total).</p>
  
  <p>Any ideas?</p>