我有一个分层数据表,我试图选择作为单个分组的XML值：
列：Id,Type,SubType,SubSubType
样本数据：
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
对于上面的数据,我想输出以下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>
即包含上表中所有行的单个xml结构,按第一列(Type)分组,并进一步按第二列(SubType)分组.
解决方法
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,'Fliers'),(3,'Data Sheets and Catalogs','Data Sheets'),(4,'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')
