嵌套 FOR XML 查询
在 SQL Server 2000 中,只能在 SELECT 查询的顶级指定 FOR XML 子句。生成的 XML 主要返回到要进行额外处理的客户端。在 SQL Server 2005 中,由于引入了 xml 数据类型和FOR XML 查询中的 TYPE 指令,因此可以在服务器上额外处理由 FOR XML 查询返回的 XML。
您可以将 FOR XML 查询结果分配给 xml 类型变量,或使用 XQuery 查询结果,将该结果分配给 xml 类型变量以进行进一步处理。
DECLARE @x xml SET @x=(SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW, TYPE) SELECT @x -- Result --<row ProductModelID="122" Name="All-Purpose Bike Stand" /> --<row ProductModelID="119" Name="Bike Wash" />
还可以使用 xml 数据类型方法之一,处理在变量
@x
中返回的 XML。例如,可以使用 value() 方法(xml 数据类型)检索ProductModelID
属性值。DECLARE @i int SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int')) SELECT @i
在以下示例中,
FOR XML
查询结果将作为 xml 类型返回,因为在FOR XML
子句中已指定了TYPE
指令。SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=119 or ProductModelID=122 FOR XML RAW, TYPE,ROOT('myRoot')
结果如下:
<myRoot> <row ProductModelID="122" Name="All-Purpose Bike Stand" /> <row ProductModelID="119" Name="Bike Wash" /> </myRoot>
由于结果为 xml 类型,因此可以对此 XML 直接指定 xml 数据类型方法之一,如以下查询所示。在此查询中,query() 方法(xml 数据类型)用于检索 <
myRoot
> 元素的第一个 <row
> 子元素。SELECT (SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=119 or ProductModelID=122 FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')
结果如下:
<row ProductModelID="122" Name="All-Purpose Bike Stand" />
此外,还可以编写嵌套
FOR XML
查询,其中内部查询的结果作为 xml 类型返回到外部查询。例如:SELECT Col1, Col2, ( SELECT Col3, Col4 FROM T2 WHERE T2.Col = T1.Col ... FOR XML AUTO, TYPE ) FROM T1 WHERE ... FOR XML AUTO, TYPE
注意上述查询的以下方面:
- 将由内部
FOR XML
查询生成的 XML 添加到由外部FOR XML
生成的 XML。 - 内部查询指定
TYPE
指令。因此,由内部查询返回的 XML 数据为 xml 类型。如果未指定 TYPE 指令,则将内部FOR XML
查询的结果作为 nvarchar(max) 返回并对 XML 数据进行实体化。
通过嵌套 FOR XML 查询,您可以更好地控制定义生成的 XML 数据的外形。
在 SQL Server 2000 中,默认情况下 RAW 和 AUTO 模式查询将生成以属性为中心的 XML。例如:
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW
以下是以属性为中心的结果:
<row ProductModelID="122" Name="All-Purpose Bike Stand" /> <row ProductModelID="119" Name="Bike Wash" />
通过指定
ELEMENTS
指令,可以将所有 XML 作为以元素为中心的 XML 进行检索。例如:SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 or ProductModelID=119 FOR XML RAW, ELEMENTS
以下是以元素为中心的结果:
<row> <ProductModelID>122</ProductModelID> <Name>All-Purpose Bike Stand</Name> </row> <row> <ProductModelID>119</ProductModelID> <Name>Bike Wash</Name> </row>
对于 SQL Server 2005 中的嵌套 FOR XML 查询,可以将 XML 构造为部分以属性为中心、部分以元素为中心。
在 SQL Server 2000 中,只能通过使用 EXPLICIT 模式编写查询来构造同级。但是,这样做可能会很麻烦。在 SQL Server 2005 中,可以通过指定嵌套 AUTO 模式 FOR XML 查询生成包括同级的 XML 层次结构。
不管使用哪种模式,嵌套 FOR XML 查询均可对说明生成的 XML 的外形进行更好的控制。可以使用这些查询替代 EXPLICIT 模式查询。
- 将由内部
示例
A. 将 FOR XML 查询与嵌套的 FOR XML 查询进行比较
以下 SELECT
查询检索 AdventureWorks 数据库中的产品类别和子类别信息。该查询中没有嵌套 FOR XML。
SELECT ProductCategory.ProductCategoryID,
ProductCategory.Name as CategoryName,
ProductSubCategory.ProductSubCategoryID,
ProductSubCategory.Name
FROM Production.ProductCategory, Production.ProductSubCategory
WHERE ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
GO
下面是部分结果:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
<ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
<ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...
如果在查询中指定 ELEMENTS
指令,则会收到以元素为中心的结果,如以下结果片段中所示:
<ProductCategory>
<ProductCategoryID>1</ProductCategoryID>
<CategoryName>Bike</CategoryName>
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<Name>Mountain Bike</Name>
</ProductSubCategory>
<ProductSubCategory>
...
</ProductSubCategory>
</ProductCategory>
然后,假设您希望生成一个 XML 层次结构,并且它是以属性为中心和以元素为中心的 XML 组合,则如以下片段中所示:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
<ProductSubCategory>
...
<ProductSubCategory>
...
</ProductCategory>
在先前的片段中,产品类别信息(如类别 ID 和类别名称)为属性。但是,子类别信息是以元素为中心的。若要构造 <ProductCategory
> 元素,则可以编写 FOR XML
查询,如下所示:
SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
结果如下:
< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />
若要在所需的 XML 中构造嵌套 <ProductSubCategory
> 元素,则可以添加嵌套 FOR XML
查询,如下所示:
SELECT ProductCategoryID, Name as CategoryName,
(SELECT ProductSubCategoryID, Name SubCategoryName
FROM Production.ProductSubCategory
WHERE ProductSubCategory.ProductCategoryID =
ProductCategory.ProductCategoryID
FOR XML AUTO, TYPE, ELEMENTS
)
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
注意上述查询的以下方面:
- 内部
FOR XML
查询检索产品子类别信息。将ELEMENTS
指令添加到内部FOR XML
,以生成以元素为中心的 XML(它将添加到由外部查询生成的 XML)。默认情况下,外部查询生成的是以属性为中心的 XML。 - 在内部查询中,指定
TYPE
指令以使结果为 xml 类型。如果未指定TYPE
,则结果作为 nvarchar(max) 类型返回,XML 数据作为实体返回。 - 外部查询也指定
TYPE
指令。因此,此查询的结果将作为 xml 类型返回到客户端。
下面是部分结果:
<ProductCategory ProductCategoryID="1" CategoryName="Bike">
<ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
<ProductSubCategory>
...
<ProductSubCategory>
...
</ProductCategory>
以下查询只是先前查询的扩展。它显示 AdventureWorks 数据库中完整的产品层次结构。包括:
- 产品类别
- 每种类别中的产品子类别
- 每种子类别的产品样式
- 每种样式的产品
您可能会发现以下查询对了解 AdventureWorks 数据库非常有用:
SELECT ProductCategoryID, Name as CategoryName,
(SELECT ProductSubCategoryID, Name SubCategoryName,
(SELECT ProductModel.ProductModelID,
ProductModel.Name as ModelName,
(SELECT ProductID, Name as ProductName, Color
FROM Production.Product
WHERE Product.ProductModelID =
ProductModel.ProductModelID
FOR XML AUTO, TYPE)
FROM (SELECT distinct ProductModel.ProductModelID,
ProductModel.Name
FROM Production.ProductModel,
Production.Product
WHERE ProductModel.ProductModelID =
Product.ProductModelID
AND Product.ProductSubCategoryID =
ProductSubCategory.ProductSubCategoryID)
ProductModel
FOR XML AUTO, type
)
FROM Production.ProductSubCategory
WHERE ProductSubCategory.ProductCategoryID =
ProductCategory.ProductCategoryID
FOR XML AUTO, TYPE, ELEMENTS
)
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
下面是部分结果:
<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
<Production.ProductSubCategory>
<ProductSubCategoryID>1</ProductSubCategoryID>
<SubCategoryName>Mountain Bikes</SubCategoryName>
<ProductModel ProductModelID="19" ModelName="Mountain-100">
<Production.Product ProductID="771"
ProductName="Mountain-100 Silver, 38" Color="Silver" />
<Production.Product ProductID="772"
ProductName="Mountain-100 Silver, 42" Color="Silver" />
<Production.Product ProductID="773"
ProductName="Mountain-100 Silver, 44" Color="Silver" />
? </ProductModel>
如果从生成产品子类别的嵌套 FOR XML
查询中删除 ELEMENTS
指令,则整个结果均以属性为中心。然后便可以编写没有嵌套的查询。添加 ELEMENTS
会使 XML 部分以属性为中心、部分以元素为中心。此结果无法通过单一级别的 FOR XML 查询生成。
B. 使用嵌套 AUTO 模式查询生成同级
以下示例显示了如何使用嵌套 AUTO 模式查询生成同级。生成此类 XML 的其他方式只有这一种,即使用 EXPLICIT 模式。但是,这样做可能会很麻烦。
此查询可构造提供销售订单信息的 XML。包括:
- 销售订单标题信息、
SalesOrderID
、SalesPersonID
和OrderDate
。AdventureWorks 将此信息存储在SalesOrderHeader
表中。 - 销售订单详细信息。这包括所订购的产品、单价和订购数量。此信息存储在
SalesOrderDetail
表中。 - 销售人员信息。这是获得订单的销售人员。
SalesPerson
表提供SalesPersonID
。对于此查询,必须将此表联接到Employee
表以查找销售人员的姓名。
后面两个不同的 SELECT
查询生成外形略有不同的 XML。
第一个查询生成的 XML 中的 <SalesPerson
> 和 <SalesOrderHeader
> 显示为 <SalesOrder
> 的同级子成员。
SELECT
(SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
(select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
from Sales.SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID =
SalesOrderHeader.SalesOrderID
FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
for xml auto, type),
(SELECT *
FROM (SELECT SalesPersonID, EmployeeID
FROM Sales.SalesPerson, HumanResources.Employee
WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As
SalesPerson
WHERE SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE
在先前的查询中,最外面的 SELECT
语句执行下列操作:
- 查询在
FROM
子句中指定的行集SalesOrder
。结果是包含一个或多个 <SalesOrder
> 元素的 XML。 - 指定
AUTO
模式和TYPE
指令。AUTO
模式将查询结果转换为 XML,TYPE
指令将结果作为 xml 类型返回。 - 包括两个以逗号分隔的嵌套
SELECT
语句。第一个嵌套SELECT
语句检索销售订单信息、标题和详细信息,第二个嵌套SELECT
语句检索销售人员信息。- 检索
SalesOrderID
、SalesPersonID
和CustomerID
的SELECT
语句本身包括另一个返回销售订单详细信息的嵌套SELECT ... FOR XML
语句(使用AUTO
模式和TYPE
指令)。
- 检索
检索销售人员信息的 SELECT
语句查询在 FROM
子句中创建的行集 SalesPerson
。若要使用 FOR XML
查询,必须提供在 FROM
子句中生成的匿名行集的名称。在本例中,提供的名称为 SalesPerson
。
下面是部分结果:
<SalesOrder>
<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
</Sales.SalesOrderHeader>
<SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...
以下查询生成的销售订单信息基本相同,只是在结果 XML 中,<SalesPerson
> 显示为 <SalesOrderDetail
> 的同级。
<SalesOrder>
<SalesOrderHeader ...>
<SalesOrderDetail .../>
<SalesOrderDetail .../>
...
<SalesPerson .../>
</SalesOrderHeader>
</SalesOrder>
<SalesOrder>
...
</SalesOrder>
以下是查询语句:
SELECT SalesOrderID, SalesPersonID, CustomerID,
(select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
from Sales.SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
FOR XML AUTO, TYPE),
(SELECT *
FROM (SELECT SalesPersonID, EmployeeID
FROM Sales.SalesPerson, HumanResources.Employee
WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
WHERE SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE
结果如下:
<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
<Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
<SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
<SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
由于 TYPE
指令将查询结果作为 xml 类型返回,因此,可以使用各种 xml 数据类型方法查询生成的 XML。有关详细信息,请参阅 xml Data Type Methods。在以下查询中,注意:
将先前的查询添加到
FROM
子句。查询结果返回为表。注意添加的XmlCol
别名。SELECT
子句对FROM
子句中返回的XmlCol
指定 XQuery。xml 数据类型的 query() 方法用于指定 XQuery。有关详细信息,请参阅 query() 方法(xml 数据类型)。SELECT XmlCol.query('<Root> { /* } </Root>') FROM ( SELECT SalesOrderID, SalesPersonID, CustomerID, (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice from Sales.SalesOrderDetail WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID FOR XML AUTO, TYPE), (SELECT * FROM (SELECT SalesPersonID, EmployeeID FROM Sales.SalesPerson, HumanResources.Employee WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson WHERE SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID FOR XML AUTO, TYPE) FROM Sales.SalesOrderHeader WHERE SalesOrderID='43659' or SalesOrderID='43660' FOR XML AUTO, TYPE ) as T(XmlCol)
C. 创建 ASPX 应用程序以便在浏览器中检索销售订单信息
在以下示例中,Aspx 应用程序执行存储过程并将销售订单信息作为 XML 返回。结果将显示在浏览器中。存储过程中的 SELECT
语句与示例 B 中的语句类似,但生成的 XML 以元素为中心。
CREATE PROC GetSalesOrderInfo AS
SELECT
(SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
(select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
from Sales.SalesOrderDetail
WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
for xml auto, type),
(SELECT *
FROM (SELECT SalesPersonID, EmployeeID
FROM Sales.SalesPerson, HumanResources.Employee
WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
WHERE SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
FOR XML AUTO, TYPE, ELEMENTS)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE
GO
以下是 .aspx 应用程序。它执行存储过程并在浏览器中返回 XML:
<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>
<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
cmd.CommandText = "GetSalesOrderInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
System.Xml.XmlReader r = cmd.ExecuteXmlReader();
System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
w.WriteStartElement("Root");
r.MoveToContent();
while(! r.EOF)
{
w.WriteNode(r, true);
}
w.WriteEndElement();
w.Flush();
}
%>
测试应用程序
- 在 AdventureWorks 数据库中创建存储过程。
- 将 .aspx 应用程序 (GetSalesOrderInfo.aspx) 保存至 c:\inetpub\www 根目录中。
- 执行应用程序 (https://server/GetSalesOrderInfo.aspx)。
D. 构造包括产品价格的 XML
以下示例查询 Production.Product
表以检索特定产品的 ListPrice
值和 StandardCost
值。若要使查询变得有趣,请在 <Price
> 元素中返回两个价格,并使每个 <Price
> 元素都有 PriceType
属性。以下是所需 XML 外形:
<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
<Price PriceType="ListPrice">133.34</Price>
<Price PriceType="StandardCost">98.77</Price>
</Production.Product>
以下是嵌套的 FOR XML 查询:
SELECT Product.ProductID,
(SELECT 'ListPrice' as PriceType,
CAST(CAST(ListPrice as NVARCHAR(40)) as XML)
FROM Production.Product Price
WHERE Price.ProductID=Product.ProductID
FOR XML AUTO, TYPE),
(SELECT 'StandardCost' as PriceType,
CAST(CAST(StandardCost as NVARCHAR(40)) as XML)
FROM Production.Product Price
WHERE Price.ProductID=Product.ProductID
FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA
注意上述查询的以下方面:
- 外部 SELECT 语句构造具有 ProductID 属性和两个 <
Price
> 子元素的 <Product
> 元素。 - 两个内部 SELECT 语句构造两个 <
Price
> 元素,每一个都具有一个 PriceType 属性和可以返回产品价格的 XML。 - 外部 SELECT 语句中的 XMLSCHEMA 指令生成用于描述结果 XML 外形的内联 XSD 架构。
为使查询能够更加有趣,可以编写 FOR XML 查询,然后针对结果编写 XQuery 以重新定形 XML,如以下查询所示:
SELECT ProductID,
( SELECT p2.ListPrice, p2.StandardCost
FROM Production.Product p2
WHERE Product.ProductID = p2.ProductID
FOR XML AUTO, ELEMENTS XSINIL, type ).query('
for $p in /p2/*
return
<Price PriceType = "{local-name($p)}">
{ data($p) }
</Price>
')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE
先前的示例使用 xml 数据类型的 query() 方法查询由内部 FOR XML 查询返回的 XML,并构造预期的结果。
结果如下:
<Production.Product ProductID="520">
<Price PriceType="ListPrice">133.3400</Price>
<Price PriceType="StandardCost">98.7700</Price>
</Production.Product>