Freigeben über

Geschachtelte FOR XML-Abfragen

In SQL Server 2000 kann die FOR XML-Klausel nur auf der obersten Ebene einer SELECT-Abfrage angegeben werden. Der resultierende XML-Code wird in erster Linie an den Client zur weiteren Verarbeitung zurückgegeben. In SQL Server 2005 kann durch die Einführung des xml-Datentyps und der TYPE-Direktive in FOR XML-Abfragen der von den FOR XML-Abfragen zurückgegebene XML-Code auf dem Server weiter verarbeitet werden.

  • Sie können das Ergebnis einer FOR XML-Abfrage einer xml-Typvariablen zuweisen oder das Ergebnis mithilfe einer XQuery-Abfrage abfragen und das daraus entstehende Ergebnis einer xml-Typvariablen zur weiteren Verarbeitung zuweisen.

    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" />

    Sie können den in der Variablen @x zurückgegebenen XML-Code zusätzlich verarbeiten, indem Sie eine der xml-Datentypmethoden verwenden. So können Sie z. B. mithilfe der value()-Methode (XML-Datentyp) den Attributwert von ProductModelID abrufen.

    DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i

    Im folgenden Beispiel wird das Ergebnis der FOR XML-Abfrage als xml-Typ zurückgegeben, da in der FOR XML-Klausel die TYPE-Direktive angegeben wurde.

    SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')

    Dies ist das Ergebnis:

    <myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>

    Da das Ergebnis dem xml-Typ entspricht, können Sie eine der xml-Datentypmethoden direkt für diesen XML-Code angeben, wie es in der folgenden Abfrage gezeigt wird. In der Abfrage wird die Methode query() (XML-Datentyp) verwendet, um das erste untergeordnete <row>-Element des Elements <myRoot> abzurufen.

    SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')

    Dies ist das Ergebnis:

    <row ProductModelID="122" Name="All-Purpose Bike Stand" />

  • Sie können auch geschachtelte FOR XML-Abfragen schreiben, bei denen das Ergebnis der inneren Abfrage als xml-Typ an die äußere Abfrage zurückgegeben wird. Beispiel:

    SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE

    Beachten Sie in der vorherigen Abfrage Folgendes:

    • Der von der inneren FOR XML-Abfrage generierte XML-Code wird dem von der äußeren FOR XML-Abfrage generierten XML-Code hinzugefügt.
    • Die innere Abfrage gibt die TYPE-Direktive an. Die von der inneren Abfrage zurückgegebenen XML-Daten gehören daher dem xml-Typ an. Wenn die TYPE-Direktive nicht angegeben wird, wird das Ergebnis der inneren FOR XML-Abfrage als nvarchar(max) zurückgegeben, und die XML-Daten werden in Entitäten geändert.

    Geschachtelte FOR XML-Abfragen ermöglichen eine bessere Steuerung der Form der resultierenden XML-Daten.

    • In SQL Server 2000 generieren im RAW- und AUTO-Modus ausgeführte Abfragen standardmäßig attributzentrierten XML-Code. Beispiel:

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW

      Dies ist das attributzentrierte Ergebnis:

      <row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

      Durch Angeben der ELEMENTS-Direktive können Sie den gesamten XML-Code als elementzentrierten Code abrufen. Beispiel:

      SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS

      Dies ist das elementzentrierte Ergebnis:

      <row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

      Für geschachtelte FOR XML-Abfragen können Sie in SQL Server 2005 XML-Code konstruieren, der zum Teil attributzentriert und zum Teil elementzentriert ist.

    • In SQL Server 2000 können Sie gleichgeordnete Elemente nur durch Schreiben von Abfragen im EXPLICIT-Modus konstruieren. Dies kann jedoch sehr aufwändig sein. In SQL Server 2005 können Sie XML-Hierarchien generieren, die gleichgeordnete Elemente enthalten, indem Sie geschachtelte FOR XML-Abfragen im AUTO-Modus angeben.

    Unabhängig davon, welchen Modus Sie verwenden, bieten geschachtelte FOR XML-Abfragen größere Steuerungsmöglichkeiten beim Beschreiben der Form des resultierenden XML-Codes. Diese Abfragen können anstelle von Abfragen im EXPLICIT-Modus verwendet werden.

Beispiele

A. Vergleichen einer FOR XML-Abfrage mit einer geschachtelten FOR XML-Abfrage

Die folgende SELECT-Abfrage ruft Informationen zur Produktkategorie und Produktunterkategorie aus der AdventureWorks-Datenbank ab. In der Abfrage gibt es keine geschachtelte FOR XML-Anweisung.

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

Dies ist das Teilergebnis:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...

Wenn Sie die ELEMENTS-Direktive in der Abfrage angeben, erhalten Sie ein elementzentriertes Ergebnis, wie es im folgenden Ergebnisfragment gezeigt wird:

<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <CategoryName>Bike</CategoryName>
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <Name>Mountain Bike</Name>
  </ProductSubCategory>
  <ProductSubCategory>
     ...
  </ProductSubCategory>
</ProductCategory>

Gehen wir nun davon aus, dass Sie eine XML-Hierarchie generieren möchten, die eine Kombination aus attributzentriertem und elementzentriertem XML-Code darstellt, wie das im folgenden Fragment gezeigt wird:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

Im oben gezeigten Fragment sind die Informationen zur Produktkategorie (z. B. die Kategorie-ID und der Kategoriename) Attribute. Allerdings sind die Informationen zur Unterkategorie elementzentriert. Zum Konstruieren des <ProductCategory>-Elements können Sie eine FOR XML-Abfrage wie die folgende schreiben:

SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Dies ist das Ergebnis:

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

Zum Konstruieren der geschachtelten <ProductSubCategory>-Elemente im angestrebten XML-Code fügen Sie anschließend eine geschachtelte FOR XML-Abfrage hinzu, wie es im folgenden Beispiel gezeigt wird:

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

Beachten Sie bei der vorstehenden Abfrage folgende Aspekte:

  • Mit der inneren FOR XML-Abfrage werden Informationen zur Produktunterkategorie abgerufen. Die ELEMENTS-Direktive wird der inneren FOR XML-Abfrage hinzugefügt, um elementzentrierten XML-Code zu generieren, der dem von der äußeren Abfrage generierten XML-Code hinzugefügt wird. Standardmäßig generiert die äußere Abfrage attributzentrierten XML-Code.
  • In der inneren Abfrage wird die TYPE-Direktive angegeben, sodass das Ergebnis den xml-Typ aufweist. Wenn TYPE nicht angegeben wird, wird das Ergebnis als nvarchar(max)-Typ zurückgegeben, und die XML-Daten werden als Entitäten zurückgegeben.
  • Die TYPE-Direktive wird auch in der äußeren Abfrage angegeben. Deshalb wird das Ergebnis dieser Abfrage als xml-Typ an den Client zurückgegeben.

Dies ist das Teilergebnis:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

Die folgende Abfrage ist lediglich eine Erweiterung der vorherigen Abfrage. Sie zeigt die vollständige Produkthierarchie in der AdventureWorks-Datenbank. Dazu gehören:

  • Produktkategorien
  • Produktunterkategorien in jeder Kategorie
  • Produktmodelle in jeder Unterkategorie
  • Produkte in jedem Produktmodell

Die folgende Abfrage kann ggf. für das Verständnis der AdventureWorks-Datenbank nützlich sein:

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

Dies ist das Teilergebnis:

<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>
     …

Wenn Sie die ELEMENTS-Direktive aus der geschachtelten FOR XML-Abfrage entfernen, mit der die Produktunterkategorien generiert werden, ist das gesamte Ergebnis attributzentriert. Sie können diese Abfrage dann ohne Schachtelung schreiben. Das Hinzufügen der ELEMENTS-Direktive ergibt einen XML-Code, der teilweise attributzentriert und teilweise elementzentriert ist. Dieses Ergebnis kann nicht durch eine FOR XML-Abfrage mit nur einer einzigen Ebene generiert werden.

B. Generieren von gleichgeordneten Elementen durch Verwenden einer geschachtelten Abfrage im AUTO-Modus

Im folgenden Beispiel wird das Generieren von gleichgeordneten Elementen durch Verwenden einer geschachtelten Abfrage im AUTO-Modus gezeigt. Die einzige Möglichkeit zum Generieren von derartigem XML-Code besteht im Verwenden des EXPLICIT-Modus. Dies kann jedoch sehr aufwendig sein.

Diese Abfrage konstruiert XML-Code, der Bestellinformationen bereitstellt. Dazu gehören:

  • Kopfzeileninformationen der Bestellungen, die Bestellungs-ID (SalesOrderID), die Vertriebsmitarbeiter-ID (SalesPersonID) und das Bestelldatum (OrderDate). AdventureWorks speichert diese Informationen in der SalesOrderHeader-Tabelle.
  • Detaillierte Bestellinformationen. Dazu gehören Angaben zu einem oder mehreren bestellten Produkten, zum Einzelpreis und zur bestellten Menge. Diese Informationen werden in der SalesOrderDetail-Tabelle gespeichert.
  • Informationen zum Vertriebsmitarbeiter. Dies ist der Vertriebsmitarbeiter, der die Bestellung entgegengenommen hat. Die SalesPerson-Tabelle stellt die Angaben für SalesPersonID bereit. Für diese Abfrage müssen Sie diese Tabelle mit der Employee-Tabelle verknüpfen, um den Namen des Vertriebsmitarbeiters zu finden.

Die beiden folgenden unterschiedlichen SELECT-Abfragen generieren XML-Code, der in seiner Form einen kleinen Unterschied aufweist.

Die erste Abfrage generiert XML-Code, in dem <SalesPerson> und <SalesOrderHeader> als nebengeordnete Unterelemente von <SalesOrder> erscheinen:

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

In der vorherigen Abfrage bewirkt die äußerste SELECT-Anweisung Folgendes:

  • Sie fragt das Rowset SalesOrder ab, das in der FROM-Klausel angegeben ist. Das Ergebnis ist ein XML-Code mit einem oder mehreren <SalesOrder>-Elementen.
  • Sie gibt den AUTO-Modus und die TYPE-Direktive an. Der AUTO-Modus wandelt das Abfrageergebnis in XML-Code um, und die TYPE-Direktive gibt das Ergebnis als xml-Typ zurück.
  • Sie schließt zwei geschachtelte SELECT-Anweisungen ein, die durch ein Komma voneinander getrennt sind. Die erste geschachtelte SELECT-Anweisung ruft die Bestellinformationen (Kopfzeile und Details) ab, und die zweite geschachtelte SELECT-Anweisung ruft die Informationen zum Vertriebsmitarbeiter ab.
    • Die SELECT-Anweisung, die SalesOrderID, SalesPersonID und CustomerID abruft, enthält eine weitere geschachtelte SELECT ... FOR XML-Anweisung (mit AUTO-Modus und TYPE-Direktive), die Detailinformationen zur Bestellung zurückgibt.

Die SELECT-Anweisung, mit der die Informationen zum Vertriebsmitarbeiter abgerufen werden, fragt ein Rowset (SalesPerson) ab, das in der FROM-Klausel erstellt wird. Damit FOR XML-Abfragen funktionsfähig sind, müssen Sie einen Namen für das anonyme Rowset bereitstellen, das in der FROM-Klausel generiert wird. In diesem Fall ist der bereitgestellte Name SalesPerson.

Dies ist das Teilergebnis:

<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>
...

Die folgende Abfrage generiert dieselben Bestellinformationen, außer dass im resultierenden XML-Code das <SalesPerson>-Element als nebengeordnetes Element von <SalesOrderDetail> erscheint:

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>
    
</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

Im Folgenden wird die Abfrage aufgeführt:

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

Dies ist das Ergebnis:

<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>

Da die TYPE-Direktive Abfrageergebnisse als xml-Typ zurückgibt, können Sie den resultierenden XML-Code mithilfe verschiedener xml-Datentypmethoden abfragen. Weitere Informationen finden Sie unter XML-Datentypmethoden. Beachten Sie in der nächsten Abfrage Folgendes:

  • Die vorherige Abfrage wird in der FROM-Klausel hinzugefügt. Das Abfrageergebnis wird als Tabelle zurückgegeben. Beachten Sie den hinzugefügten XmlCol-Alias.

  • Die SELECT-Klausel gibt eine XQuery-Abfrage für XmlCol an, das in der FROM-Klausel zurückgegeben wird. Die XQuery-Abfrage wird mit der query()-Methode des xml-Datentyps angegeben. Weitere Informationen finden Sie unter query()-Methode (xml-Datentyp).

    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. Erstellen einer ASPX-Anwendung zum Abrufen von Bestellinformationen im Browser

Im folgenden Beispiel führt eine ASPX-Anwendung eine gespeicherte Prozedur aus und gibt Bestellinformationen als XML-Code zurück. Das Ergebnis wird im Browser angezeigt. Die SELECT-Anweisung in der gespeicherten Prozedur entspricht der Anweisung in Beispiel B, außer dass der resultierende XML-Code elementzentriert ist.

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

Dies ist die ASPX-Anwendung. Sie führt die gespeicherte Prozedur aus und gibt den XML-Code im Browser zurück:

<%@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();
}
%>
So testen Sie die Anwendung
  1. Erstellen Sie die gespeicherte Prozedur in der AdventureWorks-Datenbank.
  2. Speichern Sie die ASPX-Anwendung in c:\inetpub\wwwroot-Verzeichnis (GetSalesOrderInfo.aspx).
  3. Führen Sie die Anwendung aus (https://server/GetSalesOrderInfo.aspx).

D. Konstruieren von XML-Code, der die Produktpreise einschließt

Das folgende Beispiel ist eine Abfrage der Production.Product-Tabelle zum Abrufen der Werte von ListPrice und StandardCost eines bestimmten Produkts. Damit die Abfrage interessant wird, werden beide Preise in einem <Price>-Element zurückgegeben, und jedes <Price>-Element verfügt über ein PriceType-Attribut. Der erwartete XML-Code hat die folgende Form:

<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>

Dies ist die geschachtelte FOR XML-Abfrage:

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

Beachten Sie in der vorherigen Abfrage Folgendes:

  • Die äußere SELECT-Anweisung konstruiert das <Product>-Element, das über ein ProductID-Attribut und zwei untergeordnete <Price>-Elemente verfügt.
  • Die beiden inneren SELECT-Anweisungen konstruieren zwei <Price>-Elemente, von denen jedes über ein PriceType-Attribut verfügt, sowie XML-Code, der den Produktpreis zurückgibt.
  • Die XMLSCHEMA-Direktive in der äußeren SELECT-Anweisung generiert das XSD-Inlineschema, das die Form des resultierenden XML-Codes beschreibt.

Um die Abfrage interessant zu machen, können Sie die FOR XML-Abfrage schreiben und dann eine XQuery-Abfrage für das Ergebnis schreiben, um den XML-Code umzuformen, wie das in der folgenden Abfrage gezeigt wird:

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

Im oben gezeigten Beispiel wird die query()-Methode des xml-Datentyps verwendet, um den durch die innere FOR XML-Abfrage zurückgegebenen XML-Code abzufragen und das erwartete Ergebnis zu konstruieren.

Dies ist das Ergebnis:

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>
  • Last updated on