Verwenden des AUTO-Modus mit FOR XML

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Wie in FOR XML (SQL Server) beschrieben, gibt der AUTO-Modus Abfrageergebnisse als geschachtelte XML-Elemente zurück. Dies bietet nicht viel Kontrolle über das Shape des XML-Codes, das aus einem Abfrageergebnis generiert wurde. Die AUTO-Modusabfragen sind nützlich, wenn Sie einfache Hierarchien generieren möchten. Allerdings können Sie durch Verwenden des EXPLICIT-Modus mit FOR XML und Verwenden des PATH-Modus mit FOR XML bessere Steuerungsmöglichkeiten und eine höhere Flexibilität in Bezug auf die Form des aus einem Abfrageergebnis generierten XML-Codes erzielen.

Jede Tabelle in der FROM-Klausel, aus der mindestens eine Spalte in der SELECT-Klausel aufgeführt ist, wird als ein XML-Element dargestellt. Die in der SELECT-Klausel aufgelisteten Spalten werden den entsprechenden Attributen oder Unterelementen zugeordnet, wenn die wahlweise Option ELEMENTS in der FOR XML-Klausel angegeben wurde.

Die XML-Hierarchie, also die Schachtelung der Elemente, im resultierenden XML-Code basiert auf der Tabellenreihenfolge, die durch die Spalten identifiziert wird, die in der SELECT-Klausel angegeben sind. Deshalb ist die Reihenfolge, in der die Spaltennamen in der SELECT-Klausel angegeben werden, von großer Bedeutung. Die erste identifizierte Tabelle von links bildet das oberste Element in dem resultierenden XML-Dokument. Die zweite Tabelle von links (identifiziert durch die Spalten in der SELECT-Anweisung) bildet ein Unterelement im obersten Element usw.

Falls ein in der SELECT-Klausel aufgeführter Spaltenname aus einer Tabelle stammt, die bereits durch eine zuvor angegebene Spalte in der SELECT-Klausel identifiziert wird, wird die Spalte als Attribut zu dem bereits erstellten Element hinzugefügt. Es wird also keine neue Ebene der Hierarchie geöffnet. Wenn die Option ELEMENTS angegeben ist, wird die Spalte als Attribut hinzugefügt.

Führen Sie z. B. die folgende Abfrage aus:

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO;

Dies ist das Teilergebnis:

<Cust CustomerID="1" CustomerType="S">
  <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...

Beachten Sie in der SELECT-Klausel Folgendes:

  • Die CustomerID verweist auf die Cust-Tabelle. Daher wird ein <Cust> Element erstellt, und CustomerID wird als Attribut hinzugefügt.

  • Als Nächstes verweisen die drei Spalten OrderHeader.CustomerID, OrderHeader.SaleOrderID und OrderHeader.Status auf die OrderHeader-Tabelle. Daher wird ein <OrderHeader> Element als Unterelement des <Cust> Elements hinzugefügt, und die drei Spalten werden als Attribute <OrderHeader>hinzugefügt.

  • Als Nächstes verweist die Cust.CustomerType-Spalte erneut auf die Cust-Tabelle, die bereits durch die Cust.CustomerID-Spalte identifiziert wurde. Aus diesem Grund wird kein neues Element erstellt. Stattdessen wird das CustomerType-Attribut dem <Cust> Zuvor erstellten Element hinzugefügt.

  • Die Abfrage gibt Aliasnamen für die Tabellennamen an. Diese Aliasnamen erscheinen als die entsprechenden Elementnamen.

  • ORDER BY ist erforderlich, um alle untergeordneten Elemente unter einem übergeordneten Element zu gruppieren.

Diese Abfrage gleicht der vorherigen Abfrage, außer dass in der SELECT-Klausel die Spalten in der OrderHeader-Tabelle vor den Spalten in der Cust-Tabelle angegeben werden. Daher wird das erste <OrderHeader> Element erstellt und anschließend das <Cust> untergeordnete Element hinzugefügt.

select OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerID,
       Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto;

Dies ist das Teilergebnis:

<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

Wenn die Option ELEMENTS der FOR XML-Klausel hinzugefügt wird, wird elementzentriertes XML zurückgegeben.

SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS

Dies ist das Teilergebnis:

<Cust>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
  </OrderHeader>
   ...
</Cust>
...

In dieser Abfrage werden die CustomerID-Werte von einer Zeile zum nächsten beim Erstellen der <Cust-Elemente> verglichen, da CustomerID der Primärschlüssel der Tabelle ist. Wenn CustomerID nicht als Primärschlüssel für die Tabelle identifiziert wird, werden alle Spaltenwerte (CustomerID, CustomerType in dieser Abfrage) von einer Zeile zur nächsten verglichen. Wenn sich die Werte unterscheiden, wird dem XML-Code ein neues <Cust-Element> hinzugefügt.

Wenn beim Vergleichen dieser Spaltenwerte eine der miteinander zu vergleichenden Spalten den Datentyp text, ntext, imageoder xmlaufweist, nimmt FOR XML an, dass die Werte sich unterscheiden und nicht miteinander verglichen werden, obwohl sie sich gleichen können. Dies liegt daran, dass das Vergleichen großer Objekte nicht unterstützt wird. Für jede ausgewählte Zeile werden die Elemente dem Ergebnis hinzugefügt. Spalten von (n)varchar(max) und varbinary(max) werden verglichen.

Wenn eine Spalte in der SELECT-Klausel keiner der in der FROM-Klausel identifizierten Tabellen zugeordnet werden kann, wie bei einer Aggregatspalte oder berechneten Spalte, wird die Spalte im XML-Dokument in der tiefsten Schachtelungsebene hinzugefügt, wenn sie in der Liste gefunden wird. Wird eine solche Spalte als erste Spalte in der SELECT-Klausel aufgeführt, wird sie dem obersten Element hinzugefügt.

Wenn das Platzhalterzeichen (*) in der SELECT-Klausel angegeben wurde, wird die Schachtelung auf die gleiche Art wie oben beschrieben bestimmt (d. h. auf der Grundlage der Reihenfolge, in der die Zeilen von der Abfrage-Engine zurückgegeben werden).

Nächste Schritte

Die folgenden Artikel enthalten weitere Informationen zum AUTO-Modus:

Siehe auch