sqlserver FOR XML查询参数EXPLICIT的实例

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


前言


在SQL SERVER中，XML查询可以指定RAW，AUTO，EXPLICIT，PATH。本文用一些实例介绍SQL SERVER中指定EXPLICIT的XML查询。

基础示例


一个简单的EXPLICIT例子：

 

  1.  
    with TestXmlExplicit
  2.  
    as
  3.  
    (
  4.  
    select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name]
  5.  
    union all
  6.  
    select 2,1,null,'女装',null
  7.  
    union all
  8.  
    select 3,2,null,null,'上衣'
  9.  
    union all
  10.  
    select 3,2,null,null,'裤子'
  11.  
    )
  12.  
    select * from TestXmlExplicit


 

结果：

 

tagparentclothes!1!categorysubcategory!2!namesubsubcategory!3!name
1    衣服     
2  1    女装   
3  2      上衣 
3  2      裤子 
注：上面空值项为null值，把它变成空白为了结果的清晰。

 


执行FOR XML EXPLICIT(就是上述SQL后添加FOR XML EXPLICIT)：

 

  1.  
    with TestXmlExplicit
  2.  
    as
  3.  
    (
  4.  
    select 1 as tag,null as parent,'衣服' as [clothes!1!category],null as [subcategory!2!name],null as [subsubcategory!3!name]
  5.  
    union all
  6.  
    select 2,1,null,'女装',null
  7.  
    union all
  8.  
    select 3,2,null,null,'上衣'
  9.  
    union all
  10.  
    select 3,2,null,null,'裤子'
  11.  
    )
  12.  
    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

 

  1.  
    with TestXmlExplicit
  2.  
    as
  3.  
    (
  4.  
    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]
  5.  
    union all
  6.  
    select 2,1,null,null,'女装',2222,null,0
  7.  
    union all
  8.  
    select 3,2,null,null,null,0,'上衣',3331
  9.  
    union all
  10.  
    select 3,2,null,null,null,0,'裤子',3332
  11.  
    )
  12.  
    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 !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，结果：

<clothes category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory>
      <name>上衣</name>
      <id>3331</id>
    </subsubcategory>
    <subsubcategory>
      <name>裤子</name>
      <id>3332</id>
    </subsubcategory>
  </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 !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

结果：
<clothes category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory>
      <name>上衣</name>
      <id>3331</id>
    </subsubcategory>
    <subsubcategory>
      <name>裤子</name>
    </subsubcategory>
  </subcategory>
</clothes>
注意裤子下面的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。
结果：
<clothes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" category="衣服" id="1111">
  <subcategory name="女装" id="2222">
    <subsubcategory>
      <name>上衣</name>
      <id>3331</id>
    </subsubcategory>
    <subsubcategory>
      <name>裤子</name>
      <id xsi:nil="true" />
    </subsubcategory>
  </subcategory>
</clothes>
虽然裤子的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
结果：
tagparentclothes!1!categoryclothes!1!cloid!idclothes!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查看架构可知：

<Schema name="Schema6" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="clothes" content="mixed" model="open">
    <AttributeType name="category" dt:type="string" />
    <AttributeType name="cloid" dt:type="id" />
    <AttributeType name="cloidref" dt:type="idref" />
    <attribute type="category" />
    <attribute type="cloid" />
    <attribute type="cloidref" />
  </ElementType>
</Schema>
<clothes xmlns="x-schema:#Schema6" category="LeeWhoeeUniversity" cloid="011" cloidref="099" />


指定 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
结果集：
tagparentclothes!1!cloidlist!idrefs
1  0   
1  0  022 
1  0  025 
注：上面空值项为null值，把它变成空白为了结果的清晰。

执行 for xml explicit,xmldata：
  1.  
    with TestXmlExplicit
  2.  
    as
  3.  
    (
  4.  
    select 1 as tag,0 as parent,null as [clothes!1!cloidlist!idrefs]
  5.  
    union all
  6.  
    select 1,0,'022'
  7.  
    union all
  8.  
    select 1,0,'025'
  9.  
     
  10.  
     
  11.  
    )
  12.  
    select * from TestXmlExplicit for xml explicit,xmldata


结果：
<Schema name="Schema4" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="clothes" content="mixed" model="open">
    <AttributeType name="cloidlist" dt:type="idrefs" />
    <attribute type="cloidlist" />
  </ElementType>
</Schema>
<clothes xmlns="x-schema:#Schema4" cloidlist="022 025"></clothes>

示例中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
结果：
tagparentclothes!1!categorysubcategory!2!namesubsubcategory!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的列可以用来排序。
结果：
<clothes category="衣服">
  <subcategory name="女装">
    <subsubcategory />
    <subsubcategory />
  </subcategory>
</clothes>


指定ELEMENT和XML的不同



指定ELEMENT会实体化数据即对特殊字符进行转义，XML则不会。
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name !element],null as [subcategory!2!name2 !xml]
union all
select 2,1,null,N'<b>女装</b>',N'<b>男装</b>'
)
select * from TestXmlExplicit for xml explicit

结果:
<clothes category="衣服">
  <subcategory>
    <name>&lt;b&gt;女装&lt;/b&gt;</name>
    <name2>
      <b>男装</b>
    </name2>
  </subcategory>
</clothes>
上面可以看到女装部分被转义，男装部分没有。


指定CDATA



with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!name!cdata]
union all
select 2,1,null,N'<b>女装</b>'
)
select * from TestXmlExplicit for xml explicit

结果：
<clothes category="衣服">
  <subcategory>
    <name><![CDATA[<b>女装</b>]]></name>
  </subcategory>
</clothes>


指定XMLTEXT



with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as  [subcategory!2!!XMLTEXT]
union all
select 2,1,null,N'女装',N' <s NAME="test" id="12">女装补充 </s>'
)
select * from TestXmlExplicit for xml explicit
结果：
<clothes category="衣服">
  <subcategory NAME="女装" id="12">女装补充</subcategory>
</clothes>

  • 因为对于红色字体 列没有指定 AttributeName，而指定了xmltext 指令，所以 <s>元素中的属性被追加到包含它的 <subcategory> 元素(即subcategory!2!NAME，不是clothes!1!category)的属性列表中。

  • 因为 <xmltext> 元素中的 NAME属性与相同元素级上检索到的 NAME 属性冲突，所以忽略 <xmltext> 元素中的此属性，即使 NAME 为 NULL 也是如此。通常情况下，属性将覆盖溢出中具有相同名称的属性。但是id属性没有冲突，所以保留。


再看另一种情况：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as [subcategory!2!!XMLTEXT]
union all
select 2,1,null,N'女装',N'<s NAME="test" id="12"><name>子元素</name></s>'
)
select * from TestXmlExplicit for xml explicit

XMLTEXT列值包含了子元素name, 因此当前元素追加到<subcategory>，<name>子元素</name>则作为子元素追加到<subcategory>，结果：
<clothes category="衣服">
  <subcategory NAME="女装" id="12">
    <name>子元素</name>
  </subcategory>
</clothes>


再看：
with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'衣服' as [clothes!1!category],null as [subcategory!2!NAME],null as [subcategory!2 !subname!XMLTEXT]
union all
select 2,1,null,N'女装',N'<s NAME="test" id="12"><name>子元素</name></s>'
)
select * from TestXmlExplicit for xml explicit

如果为XMLTEXT指定AttributeName(如subname)，则<s>的属性追加到<subname>，然后作为子元素追加到<subcategory>，结果：
<clothes category="衣服">
  <subcategory NAME="女装">
    <subname NAME="test" id="12">
      <name>子元素</name>
    </subname>
  </subcategory>
</clothes>




总结


以上对指定EXPLICIT的XML查询就介绍完了，下一篇文章将继续用实例介绍SQL SERVER中的XML查询：指定PATH查询。

 

 
文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树SQL高级技巧CTE和递归查询24239 人正在系统学习中
posted @ 2022-11-14 20:11  yclizq  阅读(59)  评论(0)  编辑  收藏  举报