title: SQL FOR XML multilevel from one pivoted table - grouping data
body: <p>This is going further from the question posted here: <a href="https://stackoverflow.com/questions/23250367/sql-for-xml-multilevel-from-one-pivoted-table">link</a>. The solution proposed worked well, but now I have a harder scenario and the same solution is not applying since SQL is not allowing to create a third nested <code>SELECT</code>.
  The scenario now is that the <code>dub</code> tag can be repeated inside each <code>location</code> and each <code>dub</code> may contain several <code>data</code> tags.
  An example:</p>
  
  <pre><code>&lt;locations&gt;
    &lt;location&gt;
      &lt;loc name="Country 1" id="1" /&gt;
      &lt;dubs&gt;
        &lt;dub name="City 1"&gt;
          &lt;data year="1950" value="2.43" /&gt;
          &lt;data year="1955" value="2.55" /&gt;
        &lt;/dub&gt;
        &lt;dub name="City 2"&gt;
          &lt;data year="1950" value="5.67" /&gt;
          &lt;data year="1955" value="4.42" /&gt;
        &lt;/dub&gt;
      &lt;/dubs&gt;
    &lt;/location&gt;
    &lt;location&gt;
      &lt;loc name="Country 2" id="2" /&gt;
      &lt;dubs&gt;
        &lt;dub bane="City ABC"&gt;
          &lt;data year="1950" value="4.54" /&gt;
          &lt;data year="1955" value="42.15" /&gt;
        &lt;/dub&gt;
      &lt;/dubs&gt;
    &lt;/location&gt;
  &lt;/locations&gt;
  </code></pre>
  
  <p>I've tried to do a third nested <code>SELECT</code> but it is not allowed:
  <a href="http://sqlfiddle.com/#!6/ab396/3" rel="nofollow noreferrer">SQL Fiddle - Not Working</a></p>
  
  <pre><code>select T.Country as 'loc/@name',
         T.ID as 'loc/@id',
         (
           select [City] as 'dub/@name',
           (
             select 1950 as 'dub/data/@year',
                    T.[1950] as 'dub/data/@value',
                    null,
                    1955 as 'dub/data/@year',
                    T.[1955] as 'dub/data/@value'
             for xml path(''), type
            ) as dub
         ) as dubs
  from YourTable as T
  for xml path('location'), root('locations'), type
  </code></pre>
  
  <p>I've modified the solution for the original question and produced the following query, which almost works:
  <a href="http://sqlfiddle.com/#!6/ab396/5" rel="nofollow noreferrer">SQL Fiddle - Almost Working</a></p>
  
  <pre><code>    select T.Country as 'loc/@name',
         T.ID as 'loc/@id',
         (
           select [City] as 'dub/@name',
                   1950 as 'dub/data/@year',
                    T.[1950] as 'dub/data/@value',
                    null,
                    1955 as 'dub/data/@year',
                    T.[1955] as 'dub/data/@value'
             for xml path(''), type
         ) as dubs
  from YourTable as T
  for xml path('location'), root('locations'), type
  </code></pre>
  
  <p>However, you'll see that the <code>data</code> tag is not being grouped inside it's <code>dub</code> tag - the <code>dub</code> tag is being recreated with a blank <code>name</code> attribute content.
  Any help?</p>