Search code examples
sqlsql-serverxmlfor-xml

SQL FOR XML multilevel from one pivoted table - grouping data


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?


Solution

  • 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
    

    :)