在 ASP.NET 中使用嵌套 FOR XML 查询
在此示例中，ASP.NET 应用程序通过在 SQL Server 中执行存储过程将 XML 返回至浏览器。该存储过程使用嵌套查询生成 XML。使用嵌套 AUTO 模式查询生成同级主题中显示了一个类似的 SELECT 语句。本示例演示了一种在 SQL Server 中使用嵌套的 FOR XML 查询来生成以元素为中心的 XML 的方法。
示例
USE AdventureWorks2008R2;
GO
CREATE PROC Sales.GetSalesOrderInfo AS
SET NOCOUNT ON;
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 SalesPerson.BusinessEntityID AS SalesPerson, Employee.BusinessEntityID
FROM Sales.SalesPerson, HumanResources.Employee
WHERE SalesPerson.BusinessEntityID = Employee.BusinessEntityID) As SalesPerson
WHERE SalesPerson.BusinessEntityID = SalesOrder.SalesPersonID
FOR XML AUTO, TYPE, ELEMENTS)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
) 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=AdventureWorks2008R2;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
cmd.CommandText = "Sales.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();
}
%>
测试应用程序
在 AdventureWorks2008R2 数据库中创建存储过程。
将 .aspx 应用程序 (GetSalesOrderInfo.aspx) 保存至 c:\inetpub\www 根目录中。
执行应用程序 (https://server/GetSalesOrderInfo.aspx)。