SQL Server: Two-level GROUP BY with XML output

Question

I have a table of hierarchical data that I am trying to select as a single, grouped XML value:

Columns: Id, Type, SubType, SubSubType

Sample data:

Id  Type                    Subtype                    SubSubType
1   Product Documentation   Brochures                  Functional Brochures
2   Product Documentation   Brochures                  Fliers
3   Product Documentation   Data Sheets and Catalogs   Data Sheets
4   Product Documentation   Data Sheets and Catalogs   Catalogs
5   Other Documentation     Other classification       User Guides

For the above data, I would like to output the following xml:

<AllTypes>
    <Type name="Product Documentation">
        <SubType name="Brochures">
            <SubSubType name="Functional Brochures"/>
            <SubSubType name="Fliers"/>
        </SubType>
        <SubType name="Data Sheets and Catalogs">
            <SubSubType name="Data Sheets"/>
            <SubSubType name="Catalogs"/>
        </SubType>
    </Type>
    <Type name="Other Documentation">
        <SubType name="Other classification">
            <SubSubType name="User Guides"/>
        </SubType>
    </Type>
</AllTypes>

i.e. a single xml structure containing all rows from the above table, grouped by the first column (Type), and further grouped by the second column (SubType).

Answer 1

declare @T table
(
  ID int,
  Type varchar(30),
  SubType varchar(30),
  SubSubType varchar(30)
)

insert into @T values
(1, 'Product Documentation', 'Brochures',                'Functional Brochures'),
(2, 'Product Documentation', 'Brochures',                'Fliers'),
(3, 'Product Documentation', 'Data Sheets and Catalogs', 'Data Sheets'),
(4, 'Product Documentation', 'Data Sheets and Catalogs', 'Catalogs'),
(5, 'Other Documentation',   'Other classification',     'User Guides')

select T1.Type as '@Name',
       (
       select T2.SubType as '@Name',
              (
              select T3.SubSubType as '@Name'
              from @T as T3
              where T3.SubType = T2.SubType and
                    T3.Type = T1.Type
              for xml path('SubSubType'), type
              )
       from @T as T2
       where T2.Type = T1.Type
       group by T2.SubType
       for xml path('SubType'), type
       )
from @T as T1
group by Type
for xml path('Type'), root('AllTypes')