Verwenden des AUTO-Modus
Wie im Thema Abrufen von XML-Dokumenten mithilfe von FOR XML beschrieben wird, gibt der AUTO-Modus Abfrageergebnisse als geschachtelte XML-Elemente zurück. Damit ist jedoch keine genaue Steuerungsmöglichkeit über die Form des XML-Codes gegeben, der aus einem Abfrageergebnis generiert wird. Die AUTO-Modusabfragen sind nützlich, wenn Sie einfache Hierarchien generieren möchten. Allerdings können Sie durch Verwenden des EXPLICIT-Modus und durch Verwenden des PATH-Modus 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. Deshalb wird ein <
Cust>-Element erstellt, dem CustomerID als Attribut hinzugefügt wird. - Als Nächstes verweisen die drei Spalten OrderHeader.CustomerID, OrderHeader.SaleOrderID und OrderHeader.Status auf die OrderHeader-Tabelle. Deshalb wird ein <
OrderHeader>-Element als Unterelement des <Cust>-Elements hinzugefügt, und die drei Spalten werden als Attribute von <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 zum zuvor erstellten <
Cust>-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. Deshalb wird zuerst das <OrderHeader>-Element erstellt und erst danach wird diesem Element das untergeordnete <Cust>-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 aus einer Zeile mit denen in der nächsten verglichen, wenn die <Cust>-Elemente erstellt werden, weil CustomerID der Primärschlüssel der Tabelle ist. Wenn CustomerID nicht als Primärschlüssel für die Tabelle identifiziert wurde, werden alle Spaltenwerte (in dieser Abfrage CustomerID und CustomerType) aus einer Zeile mit denen in der nächsten Zeile verglichen. Wenn die Werte voneinander abweichen, 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, image oder xml aufweist, nimmt FOR XML an, dass die Werte sich unterscheiden und nicht miteinander verglichen werden, obwohl sie sich gleichen können. Das liegt daran, dass das Vergleichen von großen Objekten nicht unterstützt wird. Für jede ausgewählte Zeile werden die Elemente dem Ergebnis hinzugefügt. Beachten Sie, dass die Spalten des Datentyps (n)varchar(max) und varbinary(max) verglichen werden.
Wenn eine Spalte in der SELECT-Klausel nicht einer der in der FROM-Klausel identifizierten Tabellen zugeordnet werden kann (z. B. bei Aggregatspalten oder berechneten Spalten), wird die Spalte dem XML-Dokument in der tiefsten Schachtelungsebene hinzugefügt, wenn sie in der Liste entdeckt 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 vom Abfragemodul zurückgegeben werden).
Wenn die Option BINARY BASE64 in der Abfrage angegeben ist, werden die Binärdaten im Base64-codierten Format zurückgegeben. Der AUTO-Modus unterstützt die URL-Codierung von Binärdaten standardmäßig (sofern die Option BINARY BASE64 nicht angegeben wurde). Das heißt, anstelle von Binärdaten wird ein Verweis (ein relativer URL auf das virtuelle Stammverzeichnis der Datenbank, in der die Abfrage ausgeführt wird) zurückgegeben. Dieser Verweis kann für den Zugriff auf die Binärdaten in nachfolgenden Vorgängen verwendet werden. Die Abfrage muss ausreichend Informationen, wie etwa Primärschlüsselspalten, bereitstellen, damit Teile des Abbildes später im XML-Dokument identifiziert werden können.
Wenn beim Angeben einer Abfrage ein Alias für die Binärspalte der Sicht verwendet wird, wird der Alias in der URL-Codierung der Binärdaten zurückgegeben. In nachfolgenden Vorgängen ist der Alias bedeutungslos, und die URL-Codierung kann nicht zum Abrufen des Abbildes verwendet werden. Sie sollten somit keine Aliasse bei der Abfrage einer Sicht mithilfe des FOR XML AUTO-Modus verwenden.
Grundlegendes zur AUTO-Modus-Heuristik beim Ermitteln der Form des zurückgegebenen XML-Codes
Der AUTO-Modus ermittelt die Form des zurückgegebenen XML-Codes auf der Grundlage der Abfrage. Um zu ermitteln, wie die Elemente geschachtelt werden sollen, vergleicht die AUTO-Modus-Heuristik die Spaltenwerte in benachbarten Zeilen. Dabei werden Spalten aller Datentypen, mit Ausnahme von ntext, text, image und xml miteinander verglichen. Spalten des Datentyps (n)varchar(max) und varbinary(max) werden verglichen.
Das folgende Beispiel veranschaulicht die Heuristik des AUTO-Modus beim Ermitteln der Form des resultierenden XML-Codes:
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
Um zu ermitteln, wo ein neues <T1>-Element beginnt, werden alle Spaltenwerte von Tabelle T1 (außer ntext, text, image und xml) verglichen, wenn der Schlüssel für die Tabelle T1 nicht angegeben wurde. Nehmen wir als Nächstes an, dass die Name-Spalte den Typ nvarchar(40) aufweist und dass die SELECT-Anweisung das folgende Rowset zurückgibt:
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
Die Heuristik des AUTO-Modus vergleicht alle Werte aus Tabelle T1, die Id- und die Name-Spalten. Da die ersten beiden Zeilen dieselben Werte für die Id- und Name-Spalten enthalten, wird ein <T1>-Elemente mit zwei untergeordneten <T2>-Elementen dem Ergebnis hinzugefügt.
Der folgende XML-Code wird zurückgegeben:
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Nehmen wir jetzt an, dass die Name-Spalte den text-Datentyp aufweist. Die Heuristik des AUTO-Modus führt keinen Vergleich der Werte für diesen Datentyp durch. Stattdessen wird angenommen, dass sich die Werte voneinander unterscheiden. Dadurch wird der folgende XML-Code generiert:
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Beispiele
Die folgenden Beispiele veranschaulichen die Verwendung des AUTO-Modus. Viele dieser Abfragen werden für XML-Dokumente mit Produktionsanweisungen für Fahrräder angegeben, die in der Instructions-Spalte der ProductModel-Tabelle gespeichert sind. Weitere Informationen zu den XML-Anweisungen finden Sie unter Darstellung des xml-Datentyps in der AdventureWorks-Datenbank.
A. Abrufen von Kunden-, Bestell- und Bestelldetailinformationen
Mit dieser Abfrage werden die Kunden-, Bestell- und Bestelldetailinformationen für einen bestimmten Kunden abgerufen.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
Da die Abfrage Tabellenaliasnamen für Cust, OrderHeader, Detail und Product identifiziert, werden durch den AUTO-Modus die entsprechenden Elemente generiert. Auch hier gilt: Durch die Reihenfolge, in der die Tabellen durch die in der SELECT-Anweisung angegebenen Spalten identifiziert werden, wird die Hierarchie dieser Elemente ermittelt.
Dies ist das Teilergebnis.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
B. Angeben von GROUP BY- und Aggregatfunktionen
Die folgende Abfrage gibt individuelle Kunden-IDs sowie die Anzahl der Bestellungen zurück, die der Kunde jeweils angefordert hat.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
Dies ist das Teilergebnis:
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
C. Angeben von berechneten Spalten im AUTO-Modus
Diese Abfrage gibt verkettete individuelle Kundennamen sowie die Bestellinformationen zurück. Das liegt daran, dass die berechnete Spalte der innersten Ebene zugeordnet ist, die an dieser Stelle entdeckt wird, was in diesem Beispiel das <SOH>-Element ist. Die verketteten Kundennamen werden als Attribute des <SOH>-Elements dem Resultset hinzugefügt.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
Dies ist das Teilergebnis:
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
Um die <IndividualCustomer>-Elemente abzurufen, die das Name-Attribut besitzen, das die Kopfzeileninformationen zu jeder Bestellung als ein Unterelement enthält, wird die Abfrage mithilfe eines untergeordneten SELECT-Ausdrucks umgeschrieben. Der innere SELECT-Ausdruck erstellt eine temporäre IndividualCustomer-Tabelle mit der berechneten Spalte, die die Namen der einzelnen Kunden enthält. Diese Tabelle wird anschließend mit der SalesOrderHeader-Tabelle verknüpft, um das Resultset zu erzielen.
Beachten Sie, dass die Sales.Individual-Tabelle individuelle Kundeninformationen speichert, wozu auch der ContactID-Wert für den Kunden gehört. Diese ContactID wird dann zum Suchen des Kontaktnamens in der Person.Contact-Tabelle verwendet.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
Dies ist das Teilergebnis:
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
D. Zurückgeben von Binärdaten
Diese Abfrage gibt das Foto eines Mitarbeiters aus der Employees-Tabelle zurück. Photo ist eine image-Spalte in der Employees-Tabelle. Der AUTO-Modus gibt standardmäßig einen Verweis (ein relativer URL auf das virtuelle Stammverzeichnis der Datenbank, in der die Abfrage ausgeführt wird) auf die Binärdaten zurück. Das EmployeeID-Schlüsselattribut muss angegeben werden, damit das Abbild der Daten später identifiziert werden kann. Beim Abrufen eines Bildverweises wie in diesem Beispiel muss der Primärschlüssel der Tabelle ebenfalls in der SELECT-Klausel angegeben werden, damit eine Zeile eindeutig identifiziert wird.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
Dies ist das Ergebnis:
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
Die gleiche Abfrage wird mit der Option BINARY BASE64 ausgeführt. Die Abfrage gibt die Binärdaten im Base64-codierten Format zurück.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
Dies ist das Ergebnis:
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
Standardmäßig wird beim Verwenden des AUTO-Modus zum Abrufen von Binärdaten statt der Binärdaten ein Verweis (ein relativer URL auf das virtuelle Stammverzeichnis der Datenbank, in der die Abfrage ausgeführt wurde) zurückgegeben. Das geschieht, wenn die Option BINARY BASE64 nicht angegeben wurde.
Wenn der AUTO-Modus einen URL-Verweis auf die Binärdaten in Datenbanken zurückgibt, die die Groß-/Kleinschreibung nicht berücksichtigen, und wenn der in der Abfrage angegebene Tabellen- oder Spaltenname nicht mit dem Tabellen- oder Spaltennamen in der Datenbank übereinstimmt, wird die Abfrage ausgeführt. Allerdings ist dann die im Verweis zurückgegebene Groß-/Kleinschreibung nicht konsistent. Beispiel:
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
Dies ist das Ergebnis:
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
Das kann insbesondere dann ein Problem darstellen, wenn dbobject-Abfragen für eine Datenbank mit Unterscheidung zwischen Groß- und Kleinschreibung ausgeführt werden. Um dieses Problem zu vermeiden, sollte die Groß-/Kleinschreibung des in Abfragen angegebenen Tabellen- oder Spaltennamens mit der Groß-/Kleinschreibung des Tabellen- oder Spaltennamens in der Datenbank übereinstimmen.
E. Grundlegendes zur Codierung
Dieses Beispiel zeigt mehrere Codierungen, die in dem Resultset auftreten.
Erstellen Sie die folgende Tabelle:
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
Fügen Sie die folgenden Daten zu der Tabelle hinzu:
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
Diese Abfrage gibt die Daten aus der Tabelle zurück. Der FOR XML AUTO-Modus ist angegeben. Binärdaten werden als Verweis zurückgegeben.
SELECT * FROM [Special Chars] FOR XML AUTO
Dies ist das Ergebnis:
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
Dies ist der Vorgang für die Codierung von Sonderzeichen in dem Ergebnis:
- Im Abfrageergebnis werden die in den Element- und Attributnamen zurückgegebenen XML- und URL-Sonderzeichen codiert, indem der hexadezimale Wert des entsprechenden Unicode-Zeichens verwendet wird. Im vorherigen Beispiel wird der Elementname <
Special Chars> als <Special_x0020_Chars> zurückgegeben. Der Attributname <Col#&2> wird als <Col_x0023__x0026_2> zurückgegeben. Sowohl XML- als auch URL-Sonderzeichen werden codiert. - Falls die Werte der Elemente oder Attribute einen der fünf standardmäßigen XML-Zeichenentitäten (', "", <, > und &) enthalten, werden diese XML-Sonderzeichen immer mithilfe der XML-Zeichencodierung codiert. Im vorherigen Resultset wird der Wert & im Wert des Attributs <
Col1> als & codiert. Das #-Zeichen bleibt jedoch unverändert, da es sich hierbei um ein gültiges XML-Zeichen und kein XML-Sonderzeichen handelt. - Falls die Werte der Elemente oder Attribute ein URL-Sonderzeichen enthalten, das in URL eine besondere Bedeutung hat, werden sie nur im DBOBJECT URL-Wert codiert. Dies geschieht nur, wenn das Sonderzeichen Teil eines Tabellen- oder Spaltennamens ist. Im Resultset wird das #-Zeichen, das Teil des Tabellennamens Col#&2 ist, als _x0023_ im DBOJBECT URL codiert.
Siehe auch
Verweis
Erstellen von XML mithilfe von FOR XML