title: SQL Server / XML: change level of nested query results
body: <p>can someone tell me how I have to change the following so that the results of the nested query (<code>items</code>) appear one level below <code>dateRange</code> instead of on the same level (which is what I get at present) ?</p>
  
  <p><strong>My SQL:</strong></p>
  
  <pre><code>SELECT      A.dateRange,
              (
                      SELECT      B.item,
                                  (
                                          SELECT      COUNT(*) AS volume
                                          FROM        LogEsc C
                                          WHERE       C.policy = B.item
                                          AND         C.EID LIKE 'PE%'
                                          AND         C.dateEsc LIKE A.dateRange + '%'
                                          FOR XML PATH(''), ELEMENTS, TYPE
                                  )
                      FROM        @temp2 B
                      ORDER BY    B.ranking, B.item
                      FOR XML PATH(''), ELEMENTS, TYPE
              ) AS items
  FROM    @temp A
  FOR XML PATH(''), ELEMENTS, TYPE
  </code></pre>
  
  <p><strong>My current XML:</strong></p>
  
  <pre><code>  &lt;dateRange&gt;2014-04&lt;/dateRange&gt;
    &lt;items&gt;
      &lt;item&gt;EIA&lt;/item&gt;
      &lt;volume&gt;17&lt;/volume&gt;
      &lt;item&gt;CG&lt;/item&gt;
      &lt;volume&gt;12&lt;/volume&gt;
      &lt;item&gt;T&lt;/item&gt;
      &lt;volume&gt;10&lt;/volume&gt;
      &lt;item&gt;AD&lt;/item&gt;
      &lt;volume&gt;9&lt;/volume&gt;
      &lt;item&gt;IR&lt;/item&gt;
      &lt;volume&gt;9&lt;/volume&gt;
      &lt;item&gt;AS&lt;/item&gt;
      &lt;volume&gt;8&lt;/volume&gt;
      &lt;item&gt;A&lt;/item&gt;
      &lt;volume&gt;8&lt;/volume&gt;
      &lt;item&gt;FS&lt;/item&gt;
      &lt;volume&gt;6&lt;/volume&gt;
      &lt;item&gt;AP&lt;/item&gt;
      &lt;volume&gt;5&lt;/volume&gt;
      &lt;item&gt;DG&lt;/item&gt;
      &lt;volume&gt;5&lt;/volume&gt;
    &lt;/items&gt;
  </code></pre>
  
  <p>Many thanks for any help with this, Mike.</p>