sql server 2005将查询结果输出为XML文档（转）
8.4 将查询结果输出为XML文档
SQL Server 2005在SQL Server 2000的基础之上扩充了有关XML数据的相应功能。为了支持XML类型，增加了相应的关键字用于注册和管理XML Schema。FOR XML和OPENXML功能(它们在SQL Server 2000中被引入用于在关系型数据和XML之间进行转换)也有所改变。下面就SQL Server 2005中为更好地支持XML数据类型所做的改动进行简单地介绍。
8.4.1 使用FOR XML命令查询XML数据
在SQL Server中，获得一个XML类型结果集的最简单的方法就是使用FOR XML命令。FOR命令最早由SQL Server 2000引入。使用FOR XML命令可以直接将查询后的结果转换为XML文档的形式。
FOR 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') ] ]
其中主要的参数如表8.9所示。
表8.9 主要的参数
|
参数名称 |
含 义 |
|
RAW [ ('ElementName') ] |
生成XML文档时，将查询所得数据集中的每行记录作为一个元素，且元素的名称为ElementName |
|
AUTO |
查询结果将以XML的层次结构返回给用户，其中查询结果中至少有一个字段将被指定为元素，而其他字段可能会被指定为属性 |
|
XMLDATA |
返回XML Schema类型的XML文档 |
|
EXPLICIT |
为返回的结果集显式地指定层级关系 |
|
PATH |
借助PATH参数，开发人员可以通过设计嵌套的FOR XML查询来组织元素和属性，例如设置用于表示复杂属性的嵌套方式等 |
|
TYPE |
用于指定查询结果将以数据类型实例的方式返回 |
|
ROOT |
用于指定根元素 |
SQL Server 2000 中的FOR XML子句并不支持在服务器上处理XML型结果集，即不能把XML结果集存在表中，或分配给一个变量。而SQL Server 2005通过提供TYPE选项，进一步提高了FOR XML功能。例如，SELECT...FOR XML TYPE语句所生成的XML数据类型的结果集可被分配给一个本地的XML变量，也可被用于INSERT 语句以实现插入XML数据类型的字段。此外，通过PATH选项，还可以设置指定字段值在XML路径中的表现形式。TYPE和PATH选项的使用大大简化了创建复杂的XML应用的工作。下面分别就FOR XML的三种主要用法进行简要地介绍。
8.4.2 FOR XML RAW
RAW模式是将要介绍的三种FOR XML命令模式中最简单、易懂的模式。实际上，指定RAW模式就意味着在生成XML结果的数据集时，将结果集中的每一行数据作为一个元素输出。
显然，使用RAW模式时，由于每一条记录被作为一个元素而输出，因此记录中的每一个字段也将被作为相应的属性(除非该字段为NULL)而输出。
【示例13】使用RAW模式。
完整的步骤如下。
(1) 在【查询编辑器】中输入面的Transact-SQL脚本：
USE Northwind
GO
SELECT TOP 10 *
FROM Products FOR XML RAW
GO
(2) 上述Transact-SQL脚本中，指定将查询的结果转换为RAW模式的XML。单击【执行】按钮，运行上述脚本，结果如图8.18所示。
图8.18 查询结果
|
提示 |
|
|
查询结果的字段名是一个GUID，SQL Server 2005为所有通过FOR XML命令生成的结果集分配一个GUID。该GUID以XML为前缀。 |
|
(3) 单击查询后返回的结果记录，可在一个新的【查询编辑器】中查看该XML，如图8.19所示。
图8.19 查询返回结果记录中包含的详细信息
从图8.19中可以看出，查询结果集中的每一行被作为一个元素，而行中的字段将被作为该元素所含的属性。
(4) 对上述Transact-SQL脚本进行修改，在FROM Products FOR XML RAW的后面添加ROOT('ProudctInfo')，如下所示：
USE Northwind
GO
SELECT TOP 10 *
FROM Products FOR XML RAW,
ROOT('ProductInfo')
GO
(5) 上述脚本以XML的形式输出查询的结果，且XML的根元素名为<ProductInfo></ProductInfo>，单击【执行】按钮，运行上述Transact-SQL脚本，结果如图8.20所示。
图8.20 查询结果
从图8.20中可见，使用ROOT命令后，生成的XML文档具有一个名为ProductInfo的根元素。
(6) 将上述Transact-SQL脚本中的ROOT替换为XMLSCHEMA，关键字XMLSCHEMA用于说明将本次查询的结果转换为XML架构集。再次运行上述脚本，结果如图8.21所示。
图8.21 查询结果
(7) 单击图8.21中的记录，在新的【查询编辑器】中显示生成的XML架构集，如图8.22所示。
图8.22 显示生成的XML架构集
8.4.3 FOR XML AUTO
如果在将查询结果集转换为XML文档时，使用了AUTO关键字，那么查询结果集将以层次结构的形式组织起来。将其中使用的查询对象作为数据表中的元素。
【示例14】使用AUTO关键字。
示例的设计过程如下。
(1) 在【查询编辑器】中输入下面的查询脚本代码：
USE Northwind
GO
SELECT TOP 10 CompanyName,ProductName,UnitPrice
FROM Suppliers S,Products P
WHERE P.SupplierID = S.SupplierID FOR XML AUTO
GO
(2) 单击【执行】按钮，运行上面的Transact-SQL脚本，生成的XML形式的结果集如图8.23所示。
从图8.23中可见，数据表别名S和P被分别作为元素，其他与使用RAW所生成的结果一样，字段仍然以元素的属性方式出现。很容易看出，使用AUTO关键字后生成的XML文档具有层次结构，使整个XML文档显得更加清晰。
图8.23 生成的XML结果集
(3) 除此之外，也可在查询语句中使用关键字ELEMENTS来指明结果集中的字段以元素的形式出现，而不是以属性的形式出现。将上述中Transact-SQL脚本中的最后一行替换为：
WHERE P.SupplierID = S.SupplierID FOR XML AUTO ELEMENT
(4) 单击【执行】按钮运行上述Transact-SQL脚本，其结果如图8.24所示。
图8.24 运行结果
(5) 使用FOR XML AUTO时同样可以使用关键字XMLSCHEMA，来生成一个XML架构，在此不再赘述。
使用FOR XML AUTO关键字时还需要注意下面一些约束：
l 查询字段中如果存在计算字段(即不能直接得出字段值的查询字段)将不能正常执行。
l 查询中出现聚合函数将无法正常执行。
解决上述问题的方法是为计算字段或应用聚合函数的字段添加相应的别名。
(6) 在【查询编辑器】中输入下面的Transact-SQL脚本：
USE Northwind
GO
SELECT TOP 10 '产品名称'+ProductName ,'单价为'+CONVERT(VARCHAR(10),UnitPrice)
FROM Products
FOR XML AUTO
GO
(7) 单击【执行】按钮，其结果如图8.25所示。
消息6809，级别16，状态1，第1 行
不能将未命名的表用作XML 标识符，也不能将未命名的列用于属性名称。请在SELECT 语句中使用AS 对未命名的列/表进行命名。
图8.25 错误消息
(8) 出现错误因为查询语句中存在计算字段，此时可以对查询字段中出现的每个计算字段起一个别名，以便完成向XML文档的转换，对上述Transact-SQL脚本改动如下：
USE Northwind
GO
SELECT TOP 10 '产品名称'+ProductName Name,'单价为'+CONVERT(VARCHAR(10),UnitPrice) Price
FROM Products
FOR XML AUTO
GO
(9) 单击【运行】按钮，生成的XML文档如图8.26所示。
(10) 在【查询编辑器】中输入下面的Transact-SQL脚本代码：
USE Northwind
GO
SELECT Region, COUNT(CompanyName)
FROM Suppliers
GROUP BY Region
FOR XML AUTO
GO
图8.26 查询结果
(11) 执行上述Transact-SQL脚本将会出现上面介绍的错误。这主要是因为该查询中存在聚合函数字段。对上述Transact-SQL脚本改动如下：
USE Northwind
GO
SELECT Region, COUNT(CompanyName) [CompanyName]
FROM Suppliers
GROUP BY Region
FOR XML AUTO
GO
(12) 单击【执行】按钮，所得的XML文档如图8.27所示。
图8.27 查询结果
8.4.5 FOR XML EXPLICIT
如果说前面介绍的AUTO和RAW选项还没有为用户提供足够的功能来设计XML型结果集的话，那么使用EXPLICIT选项后，用户就可以根据自己的需要来定制XML型结果集了。
使用FOR XML EXPLICIT选项后，查询结果集将被转换为XML文档。该XML文档的结构与结果集中的结果一致。因此，从设计查询语句时就应开始考虑最终生成的XML文档。
在EXPLICIT模式中，SELECT语句中的前两个字段必须分别命名为TAG和PARENT。TAG和PARENT是元数据字段，使用它们可以确定查询结果集的XML文档中元素的父子关系，即嵌套关系。
其中TAG字段是查询字段列表中的第一个字段。TAG字段用于存储当前元素的标记值。标记号可以使用的值是1到255。
PARENT字段用于存储当前元素的父元素标记号。如果这一列中的值是 null，该行就会被放置在XML层次结构的顶层。
在添加上述两个附加字段后，开发人员就可以轻松地定义元素之间的关系，即层次关系。此时，只需按顺序完成以下步骤即可：
l 使用TAG字段为每一个将要作为元素在XML文档中输出的别名(通常可以使用表名)定义标号。
l 使用PARENT字段为本元素指定一个父元素标号，与该标号对应的元素，将成为本元素的父元素(NULL或0表示本元素为根元素)。
l 在查询语句中定义需要被输出为元素的别名(可以使用表名)，其形式通常为：
[元素名!与该元素相关的标号!属性名]
【示例15】创建一个FOR XML EXPLICIT查询。
下面按照上面介绍的步骤创建一个FOR XML EXPLICIT查询，步骤如下。
(1) 在【查询编辑器】中输入下面的Transact-SQL脚本代码：
USE Northwind
GO
SELECT 1 AS Tag,
0 AS Parent,
CompanyName AS [CustomerName!1!CompanyName],
NULL AS [Orders !2! OrderID]
FROM Customers
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
CompanyName,
OrderId AS [Orders!2!OrderID]
FROM Customers C,Orders O
WHERE C.CustomerID = O.CustomerID
ORDER BY [CustomerName!1!CompanyName],
[Orders !2! OrderID]
FOR XML EXPLICIT
GO
上述Transact-SQL脚本中，定义的第一个元素建立在查询Select CompanyName FROM Customers之上。由于本示例希望得到具有以下层次结构的XML文档：
因此需要做的第一件事是为其设置一个tag值，这里设置为1，然后将其parent设置为0，因为该元素为顶层元素。为该元素指定相应的元素名，本示例将其命名为CustomerName，然后为该元素指定相应的属性。接下来为订单记录号指定标记，可将其指定为1之后的任何数(<255)，然后通过将其parent指定为1来指明其父级元素为CustomerName。
(2) 单击【执行】按钮，运行上述Transact-SQL脚本，其结果如图8.28所示。
图8.28 运行Transact-SQL脚本的结果
8.4.6 FOR XML的其他选项
除上面介绍的常用选项之外，FOR XML语句还提供了其他一些选项，下面针对这些选项进行简要地介绍。
1. FOR XML PATH
除选项EXPLICIT之外，SQL Server 2005还提供了一种较为简便的方法来定义元素之间的层级关系，即FOR XML PATH选项。PATH选项使用嵌套的FOR XML查询有机地将元素和属性组合在一起。
【示例16】FOR XML PATH选项的应用。
设计过程如下。
(1) 在【查询编辑器】中输入下面的Transact-SQL脚本：
USE Northwind
GO
SELECT TOP 2
OrderID AS [@OrderID],
CustomerID AS [BaseInfo/CustomerID],
EmployeeID AS [BaseInfo/EmployeeID],
OrderDate AS [DateInfo/OrderDate],
RequiredDate AS [DateInfo/RequireDate],
ShippedDate AS [DateInfo/ShippedDate],
Freight AS [Transport/Freight],
ShipName AS [Transport/ShipName],
ShipAddress AS[Address/ShipAddress],
ShipCity AS[Address/City],
ShipRegion AS[Address/Region],
ShipPostalCode AS[Address/PostalCode],
ShipCountry AS[Address/Country]
FROM Orders FOR XML PATH
上述Transact-SQL脚本中使用PATH选项来表示输出的查询结果集将以层级的方式输出到XML文档中，而输出的格式为：
字段名 AS [元素名/子元素名]
其中，“元素名”用于定义该元素的别名，而“子元素名”则与当前“字段名”绑定，即“子元素名”的值为“字段名”的值。
(2) 单击【执行】按钮运行上述Transact-SQL脚本，其结果如图8.29所示。
(3) 如果需要将某一字段定义为某元素的属性，可以使用如下形式：
字段名 AS [元素名/@属性名]
其中“字段名”与“属性名”绑定。将上述Transact-SQL脚本改动如下：
USE Northwind
GO
SELECT TOP 2
OrderID ,
CustomerID AS [BaseInfo/@CustomerID],
EmployeeID AS [BaseInfo/@EmployeeID],
OrderDate AS [DateInfo/@OrderDate],
RequiredDate AS [DateInfo/@RequireDate],
ShippedDate AS [DateInfo/@ShippedDate],
Freight AS [Transport/@Freight],
ShipName AS [Transport/@ShipName],
ShipAddress AS[Address/@ShipAddress],
ShipCity AS[Address/@City],
ShipRegion AS[Address/@Region],
ShipPostalCode AS[Address/@PostalCode],
ShipCountry AS[Address/@Country]
FROM Orders FOR XML PATH
图8.29 查询结果
(4) 单击【执行】按钮，运行上述脚本，其结果如图8.30所示。
图8.30 运行上述脚本的结果
如果不能使用ROW作为顶层元素，可以使用PATH('元素名')的方式将顶层元素命名为“元素名”。
2. FOR XML BINARY BASE64
【示例17】FOR XML BINARY BASE64的应用。
完整的设计过程如下。
(1) 在【查询编辑器】中输入下面的Transact-SQL脚本：
USE Northwind
GO
SELECT * FROM Categories
FOR XML RAW
GO
(2) 单击【执行】按钮，运行上述Transact-SQL脚本，【查询编辑器】将向用户报告以下错误：
消息6829，级别16，状态1，第1 行
FOR XML EXPLICIT 和RAW 模式当前不支持将列'Picture' 中的二进制数据作为URL 进行寻址。请删除此列，或者使用BINARY BASE64 模式，或者使用 'dbobject/TABLE[@PK1='V1']/@COLUMN' 语法直接创建URL。
出现上述错误的原因在于，Categories中的字段Picture为二进制大数据(BLOB)，因此无法对其进行文本化。此时可使用FOR XML语句的BINARY BASE64选项。BINARY BASE64功能是将任何二进制数据以基于64位的编码形式返回。
(3) 将上述Transact-SQL脚本修改如下：
USE Northwind
GO
SELECT * FROM Categories
FOR XML RAW,
BINARY BASE64
GO
(4) 单击【执行】按钮，其结果如图8.31所示。
图8.31 运行Transact-SQL脚本
(5) 由此可见，二进制大对象数据字段以基于64位的数字被返回。该选项同样适用于AUTO和EXPLICIT选项。不过，如果使用AUTO选项时没有使用BINARY BASE64选项，AUTO选项会自动地将二进制大对象数据转换为指向该对象的引用信息。在【查询编辑器】中输入下面的Transact-SQL脚本：
USE Northwind
GO
SELECT * FROM Categories
FOR XML AUTO,
BINARY BASE64
GO
(6) 单击【执行】按钮，其结果如图8.32所示。
图8.32 查询结果
3. FOR XML TYPE
既然查询结果可以输出为XML文档，那么是否可以将输出的XML文档定义为一个XML类型变量呢？回答是肯定的。此时，需要使用FOR XML TYPE选项。
【示例18】FOR XML TYPE应用。
完整的设计过程如下。
(1) 在【查询编辑器】中输入下面的Transact-SQL脚本：
USE Northwind
GO
DECLARE @xmlType AS XML
SET @xmlType = (SELECT TOP 10 *
FROM [Order Details]
FOR XML AUTO ,TYPE)
SELECT @xmlType
GO
(2) 单击【执行】按钮运行上述Transact-SQL脚本。其中Select @xmlType语句返回的XML文档如图8.33所示。
图8.33 运行上述Transact-SQL脚本的结果
