SQL Server: Two-level GROUP BY with XML output -

i have table of hierarchical data trying select 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 , catalogs   data sheets 4   product documentation   data sheets , catalogs   catalogs 5   other documentation     other classification       user guides 

for above data, output following xml:

<alltypes>     <type name="product documentation">         <subtype name="brochures">             <subsubtype name="functional brochures"/>             <subsubtype name="fliers"/>         </subtype>         <subtype name="data sheets , 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. single xml structure containing rows above table, grouped first column (type), , further grouped second column (subtype).

declare @t table (   id int,   type varchar(30),   subtype varchar(30),   subsubtype varchar(30) )  insert @t values (1, 'product documentation', 'brochures',                'functional brochures'), (2, 'product documentation', 'brochures',                'fliers'), (3, 'product documentation', 'data sheets , catalogs', 'data sheets'), (4, 'product documentation', 'data sheets , catalogs', 'catalogs'), (5, 'other documentation',   'other classification',     'user guides')  select t1.type '@name',        (        select t2.subtype '@name',               (               select t3.subsubtype '@name'               @t t3               t3.subtype = t2.subtype ,                     t3.type = t1.type               xml path('subsubtype'), type               )        @t t2        t2.type = t1.type        group t2.subtype        xml path('subtype'), type        ) @t t1 group type xml path('type'), root('alltypes')