SQL FOR XML multilevel from one pivoted table - grouping data

Question

This is going further from the question posted here: link. 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 SELECT. The scenario now is that the dub tag can be repeated inside each location and each dub may contain several data tags. An example:

<locations>
  <location>
    <loc name="Country 1" id="1" />
    <dubs>
      <dub name="City 1">
        <data year="1950" value="2.43" />
        <data year="1955" value="2.55" />
      </dub>
      <dub name="City 2">
        <data year="1950" value="5.67" />
        <data year="1955" value="4.42" />
      </dub>
    </dubs>
  </location>
  <location>
    <loc name="Country 2" id="2" />
    <dubs>
      <dub bane="City ABC">
        <data year="1950" value="4.54" />
        <data year="1955" value="42.15" />
      </dub>
    </dubs>
  </location>
</locations>

I've tried to do a third nested SELECT but it is not allowed: SQL Fiddle - Not Working

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

I've modified the solution for the original question and produced the following query, which almost works: SQL Fiddle - Almost Working

    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

However, you'll see that the data tag is not being grouped inside it's dub tag - the dub tag is being recreated with a blank name attribute content. Any help?

Answer 1

Thanks, Gasper, you were very helpful! (Gasper is my imaginary friend)

I found the solution, below: SQL Fiddle

select Country as 'loc/@name',
       ID as 'loc/@id',
       (
         select [City] as 'dub/@name',
           (
               select 1950 as 'data/@year',
                  [1950] as 'data/@value',
                  null,
                  1955 as 'data/@year',
                  [1955] as 'data/@value'
               from YourTable as D
               where Country = C.Country
               and City = T.City
               for xml path(''), type
             ) as 'dub/datavalues'
         from
         (
           select distinct City
           from YourTable
           where Country = C.Country
         ) as T
         for xml path(''), type
       ) as 'loc/dubs'
from
(
  select distinct Country, ID
  from YourTable
) as C
for xml path(''), root('locations'), type

:)