SQL SERVER中XML查询：FOR XML指定EXPLICIT
前言
在SQL SERVER中，XML查询可以指定RAW，AUTO，EXPLICIT，PATH。本文用一些实例介绍SQL SERVER中指定EXPLICIT的XML查询。
基础示例
一个简单的EXPLICIT例子：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name]
union all
select 2,1,null,'女装',null
union all
select 3,2,null,null,'上衣'
union all
select 3,2,null,null,'裤子'
)
select * from TestXmlExplicit
结果：
| tag |
parent |
clothes!1!category |
subcategory!2!name |
subsubcategory!3!name |
| 1 |
|
衣服 |
|
|
| 2 |
1 |
|
女装 |
|
| 3 |
2 |
|
|
上衣 |
| 3 |
2 |
|
|
裤子 |
注：上面空值项为null值，把它变成空白为了结果的清晰。
执行FOR XML EXPLICIT(就是上述SQL后添加FOR XML EXPLICIT)：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name]
union all
select 2,1,null,'女装',null
union all
select 3,2,null,null,'上衣'
union all
select 3,2,null,null,'裤子'
)
select * from TestXmlExplicit FOR XML EXPLICIT 结果：
分析FOR XML EXPLICIT
TAG和PARENT列是必须的。PARENT为NULL或0的是根目录。列名上叹号间的数字对应TAG。
拿三级目录来说，在一级和二级目录对应列上设为NULL值即可，因为三级目灵只关心三级目录列就行了。如TAG为3的两行，clothes!1!category和subcategory!2!name没有对应值。
任意指定同级目录
TAG数字不代表目录级别！下面是新同级目录的例子：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name],null as [newcategoryname!4!name]
union all
select 2,1,null,'女装',null,null
union all
select 3,2,null,null,'上衣',null
union all
select 4,2,null,null,null,'裤子'
)
select * from TestXmlExplicit for xml explicit
结果：
可以看出，三级目录中的元素名称已经更改成newcategoryname ，而”裤子“所在级别并不是四级目录。
为所有目录添加新属性
现在给每级目录添加属性ID，新SQL语句如下：
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],null as [subsubcategory!3!id]
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,'裤子',3332
)
select * from TestXmlExplicit
结果：
注：上面空值项为null值，把它变成空白为了结果的清晰。
运行FOR XML EXPLICIT
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],null as [subsubcategory!3!id]
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,'裤子',3332
)
select * from TestXmlExplicit FOR XML EXPLICIT
结果
指定ELEMENT 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!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,'裤子',3332
)
select * from TestXmlExplicit for xml explicit
红色字体部分指定了element，结果：
上衣
3331
裤子
3332
需要注意的是，如果指定element的项为空值，结果集中将不显示该项，如： with TestXmlExplicit
as
(
select 1 as tag,null as parent,'衣服' as [clothes!1!category],1111 as [clothes!1!id],null as [subcategory!2!name],nul |