l as [subcategory!2!id],null as [subsubcategory!3!name!element],null as [subsubcategory!3!id!element]
union all
select 2,1,null,null,'女装',2222,null,0
union all
select 3,2,null,null,null,0,'上衣',3331
union all
select 3,2,null,null,null,0,'裤子',null
)
select * from TestXmlExplicit for xml explicit
结果：
上衣
3331
裤子
注意裤子下面的ID项不再存在，如果你想要显示具有空值的该项，需要指定elementxsinil。
指定ELEMENTXSINIL with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],1111 as [clothes!1!id],null as [subcategory!2!name],null as [subcategory!2!id],null as [subsubcategory!3!name!element],null as [subsubcategory!3!id!ELEMENTXSINIL]
union all
select 2,1,null,null,'女装',2222,null,0
union all
select 3,2,null,null,null,0,'上衣',3331
union all
select 3,2,null,null,null,0,'裤子',null
)
select * from TestXmlExplicit for xml explicit
注意红色字体部分，最后一列指定了ELEMENTXSINIL，为了测试，把最后一行最后一列的值改为NULL。 结果：
上衣
3331
裤子
虽然裤子的id项是空值，但依然显示。
指定 ID 和 IDREF with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'LeeWhoeeUniversity' as [clothes!1!category],'011' as [clothes!1!cloid!id],'099' as [clothes!1!cloidref!idref]
)
select * from TestXmlExplicit
结果：
| tag |
parent |
clothes!1!category |
clothes!1!cloid!id |
clothes!1!cloidref!idref |
| 1 |
|
LeeWhoeeUniversity |
011 |
099 |
注：上面空值项为null值，把它变成空白为了结果的清晰。
执行for xml explicit,xmldata：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'LeeWhoeeUniversity' as [clothes!1!category],'011' as [clothes!1!cloid!id],'099' as [clothes!1!cloidref!idref]
)
select * from TestXmlExplicit for xml explicit,xmldata
红色字体为XML指定了ID和IDREF类型，通XMLDATA查看架构可知：
指定 IDREFS 指令 with TestXmlExplicit
as
(
select 1 as tag,0 as parent,null as [clothes!1!cloidlist!idrefs]
union all
select 1,0,'022'
union all
select 1,0,'025'
)
select * from TestXmlExplicit
结果集：
| tag |
parent |
clothes!1!cloidlist!idrefs |
| 1 |
0 |
|
| 1 |
0 |
022 |
| 1 |
0 |
025 |
注：上面空值项为null值，把它变成空白为了结果的清晰。
执行 for xml explicit,xmldata：
with TestXmlExplicit
as
(
select 1 as tag,0 as parent,null as [clothes!1!cloidlist!idrefs]
union all
select 1,0,'022'
union all
select 1,0,'025'
)
select * from TestXmlExplicit for xml explicit,xmldata
结果：
示例中cloidlist的类型是idrefs。IDREFS类型的元素也有多个值。因此，必须使用单独的 SELECT 子句来重复使用相同的标记、父级和键列信息。然后，ORDER BY 必须确保组成IDREFS 值的行的序列成组显示在它们的父元素下。
指定 HIDE HIDE顾名思义，隐藏信息。 with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name!hide]
union all
select 2,1,null,N'女装',null
union all
select 3,2,null,null,N'上衣'
union all
select 3,2,null,null,N'裤子'
)
select * from TestXmlExplicit
结果：
| tag |
parent |
clothes!1!category |
subcategory!2!name |
subsubcategory!3!name!hide |
| 1 |
|
衣服 |
|
|
| 2 |
1 |
|
女装 |
|
| 3 |
2 |
|
|
上衣 |
| 3 |
2 |
|
|
裤子 |
注：上面空值项为null值，把它变成空白为了结果的清晰。
执行for xml explicit： with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name!hide]
union all
select 2,1,null,N'女装',null
union all
select 3,2,null,null,N'上衣'
union all
select 3,2,null,null,N'裤子'
)
select * from TestXmlExplicit for xml explicit
三级目录的subsubcategory!3!name不会显示在结果中，但是HIDE的列可以用来排序。 结果：
指定ELEMENT和XML的不同 指定ELEME |