4.4.2 使用FOR XML子句

4.4.2 使用FOR XML子句

FOR XML是在SQL Server 2000中引用的一个功能，旨在将T-SQL查询结果以XML格式返回（而不是标准行集）。

在SQL Server 2000中，FOR XML子句只能生成XML文档片断，而且只能以以文本的形式返回给客户端，无法用T-SQL语句直接操作FOR XML的结果，故FOR XML子句的使用率并不太高。在SQL Server 2005中，这一情况得到了改善，生成的XML结果，即可以是XML文档，也可以是XML文档片断；在返回的数据类型上，可以是xml类型（通过指定type指令），也可以是nvarchar(max)类型（不指定type指令），无论返回的是哪种类型，都可以定义相应的变量来缓存和做进一步的操作。
FOR XML子句的语法结构如下：

FOR XML mode [, CommonDirectives] [, PrivateDirectives]

它由3部分组成：mode（模式）、CommonDirectives（公用指令，适用于所有模式的指令）及PrivateDirectives（专用指令，仅适用于某种或者某几种模式的指令）。其中，模式是必选的，它决定生成XML的形状；而指令是可选的，它们控制生成XML的一些细节。

1．模式

模式可以分为4种类型：AUTO、RAW、EXPLICIT和PATH。

（1）AUTO

AUTO模式将基于指定SELECT语句的方式来使用试探性方法在XML结果中生成嵌套。用户对生成的XML的形状具有最低限度的控制能力。

在FROM子句内，每个在SELECT子句中至少有一列被列出的输入表都表示为一个XML元素。生成的XML文档中的XML层次结构（即元素嵌套）基于由SELECT子句中指定的列所标识的输入表的顺序，因此，在SELECT子句中指定的列名的顺序非常重要，最左侧第一个被标识的输入表形成所生成的XML文档中的顶级元素；由SELECT语句中的列所标识的最左侧第二个输入表形成顶级元素内的子元素，依此类推。如果SELECT子句中列出的列名来自由SELECT子句中以前指定的列所标识的输入表，该列将作为已创建的元素的属性添加，而不是在层次结构中打开一个新级别。

下面的示例演示AUTO模式。示例生成当前库中，所有表的结构：在生成的XML结构中，表的属性为第一层次的元素；列的属性为第二层次的元素；列的数据类型为第三层次的元素。要实现这种效果，这里构建了3个CTE，分别用于反映表、列和数据类型，然后通过JOIN关联它们，最后使用AUTO模式来得到最终结果。

WITH
TB AS(
SELECT 
id = object_id, table_name = name,
create_date, modify_date
FROM sys.tables
),
COL AS(
SELECT 
id = object_id, column_name = name,
max_length, precision, scale,
user_type_id
FROM sys.columns
),
TYP AS(
SELECT 
user_type_id, type_name = name
FROM sys.types
)
SELECT
TB.table_name, TB.create_date, TB.modify_date,
COL.column_name,
TYP.type_name,
COL.max_length, COL.precision, COL.scale
FROM TB
INNER JOIN COL
ON TB.id = COL.id
INNER JOIN TYP
ON COL.user_type_id = TYP.user_type_id
FOR XML AUTO
ORDER BY COL.id, COL.column_name

上述示例运行的结果片断如下，从结果可以看到，AUTO模式实现了预想的需求：通过输入表在结果中出现的顺序来控制生成的XML嵌套。

<TB table_name="bmk" create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
<COL column_name="id" max_length="4" precision="10" scale="0">
<TYP type_name="int" />
</COL>
<COL column_name="KH_D" max_length="4" precision="10" scale="0">
<TYP type_name="int" />
</COL>
<COL column_name="KM_H" max_length="4" precision="10" scale="0">
<TYP type_name="int" />
</COL>
<COL column_name="KS_H" max_length="4" precision="10" scale="0">
<TYP type_name="int" />
</COL>
</TB>

值得一提的是，在示例中，故意将COL的部分列放在TYP后面，从结果显示，这些列并未在XML结果中新开一个层次，而是被作为已经存在的COL元素一个属性添加到结果中。需要强调的是“生成的XML文档中的XML层次结构（即元素嵌套）基于由SELECT子句中指定的列所标识的输入表的顺序”，因此，如果将COL的所有列放在TYP的列后面，则生成的结构XML中，TYP的层次将出现在COL之前，实现的效果如下。

<TB table_name="bmk" create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
<TYP type_name="int">
<COL column_name="id" max_length="4" precision="10" scale="0" />
<COL column_name="KH_D" max_length="4" precision="10" scale="0" />
<COL column_name="KM_H" max_length="4" precision="10" scale="0" />
<COL column_name="KS_H" max_length="4" precision="10" scale="0" />
</TYP>
</TB>

使用AUTO模式生成嵌套的XML时，SELECT结果集的数据顺序需要特别注意，它会影响到嵌套结构的表现形式。例如，在下面的示例中，通过AUTO模式生成一个嵌套的XML结构，但使用ORDER BY子句故意打乱结果顺序，结果生成的XML结构中的元素顺序也是按照ORDER BY子句的顺序。当然，最终生成的XML结果不会影响到数据的检索，但看起来会比较别扭，影响整个XML数据的人工可读性。

WITH
A AS(
SELECT id = 1 UNION ALL
SELECT id = 2
),
B AS(
SELECT id = 1, value = 1 UNION ALL
SELECT id = 1, value = 3 UNION ALL
SELECT id = 2, value = 2
)
SELECT
A.id,
B.value
FROM A, B
WHERE A.id = B.id
ORDER BY B.value
FOR XML AUTO

上述示例的执行结果如下：

<A id="1">
<B value="1" />
</A>
<A id="2">
<B value="2" />
</A>
<A id="1">
<B value="3" />
</A>

（2）RAW

RAW模式将查询结果集中的每一行转换为带有通用标识符“<row>”或可能提供元素名称的XML元素（通过RAW('ElementName')中的ElementName参数提供元素名）。

从语法结构看，RAW模式与AUTO模式一样，但它们生成的XML形状是不一样的，这点需要特别注意，RAW模式不会使用试探性方法在XML结果中生成嵌套。

下面的示例沿用AUTO模式的最后一个示例，仅仅是将模式修改为RAW。

WITH
A AS(
SELECT id = 1 UNION ALL
SELECT id = 2
),
B AS(
SELECT id = 1, value = 1 UNION ALL
SELECT id = 1, value = 3 UNION ALL
SELECT id = 2, value = 2
)
SELECT
A.id,
B.value
FROM A, B
WHERE A.id = B.id
ORDER BY B.value
FOR XML RAW

由于RAW模式不会使用试探性方法在XML结果中生成嵌套，它仅仅是将查询结果集中的每一行转换为带有通用标识符“<row>”或可能提供元素名称的XML元素，故其执行结果如下：

<row id="1" value="1" />
<row id="2" value="2" />
<row id="1" value="3" />

（3）EXPLICIT

AUTO模式使用试探性方法在XML结果中生成嵌套，用户对对生成的XML的形状控制能力有限。EXPLICIT模式提供了更为灵活的控制能力，它根据定义产生XML树的形状。使用此模式时，必须以一种特定的方式编写查询，以便显式指定所需嵌套的其他信息。

首先，查询必须包含两个整数类型的列：tag和parent，以提供层次结构信息。

l 第一列必须是tag列，此列提供当前元素的标记号；

l 第二列必须是parent列，此列提供父元素的标记号。此列值为0或NULL表明相应的元素没有父级，该元素做为顶级元素添加到结果XML文档中。

查询中的其他列提供生成结构XML文档的节点信息，列名需要遵照如下的命名规则：

ElementName!TagNumber!AttributeName!Directive

在上述命名规则中，ElementName是所生成元素的通用标识符；TagNumber是分配给元素的唯一标记值，该值与tag和parent列配合，用于确定结果XML文档中的元素的嵌套；AttributeName提供要在指定的ElementName中构造的属性的名称（没有指定Directive的情况下）；如果指定了Directive为xml、cdata或element之一，则此值用于构造ElementName的子元素，并且此列值将添加到该子元素。当指定了Directive时，AttributeName可以为空。

Directive提供有关XML构造的其他信息，它是可选的。Directive有两种用途：

¡  将值编码为ID、IDREF和IDREFS。可以将ID、IDREF和IDREFS关键字指定为Directives，这些指令将覆盖属性类型。

¡  使用Directive来指示如何将字符串数据映射到XML。可以将hide、element、xml、xmltext、elementxsinil和cdata关键字用作Directive。

下面通过一个示例说明EXPLICIT模式的用法。示例AUTO模式示例一样的需求：生成当前库中，所有表的结构：表的属性为第一层次的元素；列的属性为第二层次的元素；列的数据类型为第三层次的元素。与AUTO模式不同的是，要求XML结构中，每层的name列（表名、列名和类型名）以文本结点的方式出现。

WITH
TB AS(
SELECT 
tag = 1, parent = 0,
name, create_date, modify_date,
object_id
FROM sys.tables
),
COL AS(
SELECT 
tag = 2, parent = 1,
name, max_length, precision, scale,
user_type_id, object_id
FROM sys.columns C
WHERE EXISTS(
SELECT * FROM TB
WHERE object_id = C.object_id)
),
TYP AS(
SELECT 
tag = 3, parent = COL.tag,
T.name,
COL.object_id, column_name = COL.name
FROM sys.types T, COL
WHERE T.user_type_id = COL.user_type_id
)
SELECT
tag, parent,
[Table!1!!element] = name,
[Table!1!create_date] = create_date,
[Table!1!modify_date] = modify_date,
[Column!2!!element] = NULL,
[Column!2!max_length] = NULL,
[Column!2!precision] = NULL,
[Column!2!scale] = NULL,
[Type!3!!element] = NULL,
[Table!1!!hide] = object_id,
[Column!2!!hide] = NULL
FROM TB
UNION ALL
SELECT
tag, parent,
[Table!1!!element] = NULL,
[Table!1!create_date] = NULL,
[Table!1!modify_date] = NULL,
[Column!2!!element] = name,
[Column!2!max_length] = max_length,
[Column!2!precision] = precision,
[Column!2!scale] = scale,
[Type!3!!element] = NULL,
[Table!1!!hide] = object_id,
[Column!2!!hide] = name
FROM COL
UNION ALL
SELECT
tag, parent,
[Table!1!!element] = NULL,
[Table!1!create_date] = NULL,
[Table!1!modify_date] = NULL,
[Column!2!!element] = NULL,
[Column!2!max_length] = NULL,
[Column!2!precision] = NULL,
[Column!2!scale] = NULL,
[Type!3!!element] = name,
[Table!1!!hide] = object_id,
[Column!2!!hide] = column_name
FROM TYP
ORDER BY [Table!1!!hide], [Column!2!!hide],tag
FOR XML EXPLICIT

在上述示例中，对于name列使用了Directive指令element，表示该列在XML结果中以文本结点的方式出现；由于EXPLICIT模式要求数据根据嵌套的层次预先排序好，因此，为了实现这种排序，这里使用了“[Table!1!!hide]”和“[Column!2!!hide]”两个列用于排序，由于这两个列仅仅是排序需要的，并不需要出现在XML结果中，故使用了Directive指令hide。运行上述代码的结果片断之一如下：

<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
id<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KH_D<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KM_H<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KS_H<Type>int</Type>
</Column>
</Table>

（4）PATH

PATH模式提供一种较简单的方法来混合元素和属性，PATH模式还是一种用于引入附加嵌套来表示复杂属性的较简单的方法。

PATH为查询结果的第1行生成行元素，行元素的默认名称为row，可以通过PATH('ElementName')中的ElementName参数指定行元素的名称。在PATH模式中，列名或列别名被作为XPath表达式来处理。这些表达式指明了如何将值映射到XML。每个XPath表达式都是一个相对XPath，它提供了项类型（例如属性、元素和标量值）以及将相对于行元素而生成的节点的名称和层次结构。表4-6列出了常用的列名表达式。

表4-6                           PATH模式下的常用列名表达式

下面的示例生成当前库中，所有表的结构。在示例中，读者可以看到列名为空、列名不包含斜杠（/）和@标记、列名包含@、列名包含斜杠（/）标记这几种情况，对比执行结果和表4-6的说明，相信可以使读者对于PATH中的列名表达式有更深入的认识。

SELECT
[@create_date] = TB.create_date,
[@modify_date] = TB.modify_date,
RTRIM(TB.name),
[Column/@max_length] = C.max_length,
[Column/@precision] = C.precision,
[Column/@scale] = C.scale,
[Column] = C.name,
[Column/Type] = T.name
FROM sys.tables TB
INNER JOIN sys.columns C
ON TB.object_id = C.object_id
INNER JOIN sys.types T
ON C.user_type_id = T.user_type_id
ORDER BY TB.object_id, C.name
FOR XML PATH('Table')

PATH模式生成的XML基于每个行的映射，运行上述代码的结果片断之一如下：

<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
id<Type>int</Type>
</Column>
</Table>
<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
KH_D<Type>int</Type>
</Column>
</Table>
<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
KM_H<Type>int</Type>
</Column>
</Table>
<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
KS_H<Type>int</Type>
</Column>
</Table>

如果要生成嵌套的XML结果，则可以使用嵌套的查询语句。下面的示例演示如何使用嵌套查询，配合PATH模式生成嵌套的XML结果。示例中使用了TYPE指令，使CROSS JOIN返回xml类型的数据列。

SELECT
[@create_date] = TB.create_date,
[@modify_date] = TB.modify_date,
[*] = TB.name,
[*] = C.Colomn
FROM sys.tables TB
CROSS APPLY(
SELECT Colomn = (
SELECT
[@max_length] = C.max_length,
[@precision] = C.precision,
[@scale] = C.scale,
[*] = name,
[*] = T.Type
FROM sys.columns C
CROSS APPLY(
SELECT Type = (
SELECT
[*] = name
FROM sys.types T
WHERE user_type_id = C.user_type_id
FOR XML PATH('Type'), TYPE)
)T
WHERE object_id = TB.object_id
ORDER BY name
FOR XML PATH('Column'), TYPE)
)C
ORDER BY object_id
FOR XML PATH('Table')

运行上述代码的结果片断之一如下：

<Table create_date="2006-06-25T08:51:41.390" modify_date="2006-06-25T08:51:41.437">
bmk<Column max_length="4" precision="10" scale="0">
id<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KH_D<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KM_H<Type>int</Type>
</Column><Column max_length="4" precision="10" scale="0">
KS_H<Type>int</Type>
</Column>
</Table>

2．公用指令

指令有时候也称之为选项。公用指令有3种：TYPE、ROOT和BINARY BASE64，它们适用于所有的FOR XML模式。

¡  TYPE指令：指示查询的XML文档结果以xml类型返回。如果未在FOR XML子句中指定TYPE子令，则查询的XML文档结果以nvarchar(max)类型返回。

¡  ROOT指令：为FOR XML生成的XML结果中添加单个顶级元素，这样可以控制生成的结果为XML文档。可以选择指定要生成的根元素名称，默认值为“root”。

¡  BINARY BASE64：指示查询所返回的任何二进制数据都用BASE64编码格式表示。当使用RAW和EXPLICIT模式检索二进制数据时，必须指定此选项；在AUTO模式中，默认情况下将二进制数据作为引用返回。

3．专用指令

专用指定仅适用于某一种或者某几种模式，表4-7列出了专用指令与它们适用的模式。

表4-7                           专用指令及它们适用的模式

如果要在生成的XML中添加命名空间，则可以使用WITH XMLNAMESPACES语句。