Utilizzo di query FOR XML nidificate in ASP.NETUse Nested FOR XML Queries in ASP.NET

In questo esempio, l'applicazione ASP.NET consente di restituire il valore XML in un browser mediante l'esecuzione di una stored procedure in SQL Server.In this example, an ASP.NET application returns XML to a browser by executing a stored procedure in SQL Server. La stored procedure consente di generare un valore XML tramite query nidificate.The stored procedure generates XML using nested queries. Un'istruzione SELECT simile è illustrata nell'argomento Generazione di elementi di pari livello tramite query nidificate in modalità AUTO.A similar SELECT statement is shown in the topic Generating Siblings by Using a Nested AUTO Mode Query. In questo esempio viene indicata la modalità per utilizzare query FOR XML nidificate per generare un valore XML incentrato sugli attributi in SQL ServerSQL Server.This example demonstrates one way to use nested FOR XML queries to generate element-centric XML in SQL ServerSQL Server.

EsempioExample

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  

L'applicazione aspx è riportata di seguito.This is the .aspx application. Viene eseguita la stored procedure e restituito il valore XML nel browser:It executes the stored procedure and returns XML in the browser:

<%@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();  
}  
%>  
Per testare l'applicazioneTo test the application
  1. Creare la stored procedure nel database AdventureWorks2012AdventureWorks2012 .Create the stored procedure in the AdventureWorks2012AdventureWorks2012 database.

  2. Salvare l'applicazione aspx nella directory c:\inetpub\wwwroot (GetSalesOrderInfo.aspx).Save the .aspx application in the c:\inetpub\wwwroot directory (GetSalesOrderInfo.aspx).

  3. Eseguire l'applicazione (http://server/GetSalesOrderInfo.aspx).Execute the application (http://server/GetSalesOrderInfo.aspx).

Vedere ancheSee Also

Utilizzo di query FOR XML nidificateUse Nested FOR XML Queries