SQL SERVER中XML查询：FOR XML指定EXPLICIT

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

结果：

<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory name="上衣" />
    <subsubcategory name="裤子" />
  </subcategory>
</clothes>

分析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
结果：

<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory name="上衣" />
    <newcategoryname name="裤子" />
  </subcategory>
</clothes>

可以看出，三级目录中的元素名称已经更改成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

结果

<clothes category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory name="上衣" id="3331" />
    <subsubcategory name="裤子" id="3332" />
  </subcategory>
</clothes>

指定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