FOR XML 模式可以是 RAW、AUTO、EXPLICIT 或 PATH。它确定产生的 XML 的形状。基本语法：[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
{
{ RAW [ ('ElementName') ] | AUTO }
[
<CommonDirectives>
[ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ]
]
| EXPLICIT
[
<CommonDirectives>
[ , XMLDATA ]
]
| PATH [ ('ElementName') ]
[
<CommonDirectives>
[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
<CommonDirectives> ::=
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ('RootName') ] ]FOX XML指定EXPLICIT一个简单的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 parentclothes!1!categorysubcategory!2!namesubsubcategory!3!name
1 NULL 衣服 NULL NULL
2 1 NULL 女装 NULL
3 2 NULL NULL 上衣
3 2 NULL NULL 裤子
执行FOR XML EXPLICIT结果(就是上述SQL后添加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
结果：
tag parentclothes!1!categoryclothes!1!idsubcategory!2!namesubcategory!2!idsubsubcategory!3!namesubsubcategory!3!id
1 NULL 衣服 1111 NULL NULL NULL NULL
2 1 NULL NULL 女装 2222 NULL 0
3 2 NULL NULL NULL 0 上衣 3331
3 2 NULL NULL NULL 0 裤子 3332
运行FOR XML EXPLICIT，结果：
<clothes category="衣服" id="1111">
<subcategory name="女装" id="2222">
<subsubcategory name="上衣" id="3331" />
<subsubcategory name="裤子" id="3332" />
</subcategory>
</clothes>
指定ELEMENTwith 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>
指定ELEMENTXSINILwith 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 和 IDREF with TestXmlExplicit
as
(
select 1 as tag,null as parent,N'乐可乐可的部落格' 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="乐可乐可的部落格" 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 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。因此，必须使用单独的 SELECT 子句来重复使用相同的标记、父级和键列信息。然后，因为对于红色字体 列没有指定 因为 <通常情况下，属性将覆盖溢出中具有相同名称的属性。但是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>
第5.1部分
第5.2部分
第5.4部分
第5.5部分
