SQL Server: Two-level GROUP BY with XML output
15,621
Solution 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')
Solution 2
select
T1.Type as name,
(
select
T2.SubType as name,
(
select T3.SubSubType as name
from Table1 as T3
where T3.Type = T1.Type and T3.SubType = T2.SubType
for xml raw('SubSubType'), type
)
from (select distinct Type, SubType from Table1) as T2
where T1.Type = T2.Type
for xml raw('SubType'), type
)
from (select distinct Type from Table1) as T1
for xml raw('Type'), root('AllTypes')
Author by
gice
Updated on June 14, 2022Comments
-
gice almost 2 years
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).
-
gice over 10 yearsding ding ding! That did the trick. I had to CAST TO XML the two inner selects, but the results are perfect. Thanks!
-
Mikael Eriksson over 10 years@user1822597 You should not need the cast. Did you forget
,type
? -
Mikael Eriksson over 10 years@user1822597 Did you try a copy paste with the code in the answer? Perhaps you forgot the comma before
type
? -
gice over 10 yearsMy bad -- the column names I used in this writeup were fictional, and "Type" was a bad choice. Your solution works perfectly as-is. Thanks again!
-
mkataja over 9 yearsI tried this, too, but the accepted answer gives nicer xml structure because of the GROUP BYs.