Teilen über

Beispiele: Verwenden des AUTO-Modus

  • Artikel

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

Die folgenden Beispiele veranschaulichen die Verwendung des AUTO-Modus. Viele dieser Abfragen werden anhand von XML-Dokumenten zur Fahrradherstellung angegeben, die in der Spalte "Anweisungen" der Tabelle "ProductModel" in der AdventureWorks2022-Beispieldatenbank gespeichert sind.

Beispiel: Abrufen von Kunden-, Bestell- und Bestelldetails

Mit dieser Abfrage werden die Kunden-, Bestell- und Bestelldetailinformationen für einen bestimmten Kunden abgerufen.

USE AdventureWorks2022;
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       Detail.SalesOrderID, Detail.LineTotal, Detail.ProductID,
       Product.Name,
       Detail.OrderQty
FROM Sales.Customer AS Cust
INNER JOIN Sales.SalesOrderHeader AS OrderHeader
    ON Cust.CustomerID = OrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail AS Detail
    ON OrderHeader.SalesOrderID = Detail.SalesOrderID
INNER JOIN Production.Product AS Product
    ON Product.ProductID = Detail.ProductID
WHERE Cust.CustomerID IN (29672, 29734)
ORDER BY OrderHeader.CustomerID,
         OrderHeader.SalesOrderID
FOR XML AUTO;

Da die Abfrage Tabellenaliasnamen für Cust, OrderHeader, Detailund 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 -Klausel angegebenen Spalten identifiziert werden, wird die Hierarchie dieser Elemente ermittelt.

Dies ist das Teilergebnis.

<Cust CustomerID="29672">
  <OrderHeader CustomerID="29672" 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="29672" SalesOrderID="47660">
    <Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
      <Product Name="Road-650 Black, 58" />
    </Detail>
  </OrderHeader>
  <OrderHeader CustomerID="29672" SalesOrderID="49857">
    <Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
      <Product Name="Women's Tights, S" />
    </Detail>
  </OrderHeader>
...
</Cust>

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

USE AdventureWorks2022;
GO
SELECT C.CustomerID, COUNT(*) AS NoOfOrders
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
On C.CustomerID = SOH.CustomerID
GROUP BY C.CustomerID
FOR XML AUTO;

Dies ist das Teilergebnis:

<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...

Beispiel: Angeben berechneter Spalten im AUTO-Modus

Diese Abfrage gibt verkettete Namen einzelner Kunden sowie die Bestellinformationen zurück. Da die berechnete Spalte an diesem Punkt der innersten Ebene zugewiesen ist, wird das <SOH> Element in diesem Beispiel verwendet. Die verketteten Kundennamen werden als Attribute des <SOH> Elements im Ergebnis hinzugefügt.

USE AdventureWorks2022;
GO
SELECT P.FirstName + ' ' + P.LastName AS Name,
       SOH.SalesOrderID
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
    ON  C.CustomerID = SOH.CustomerID
INNER JOIN Person.Person AS P
    ON P.BusinessEntityID = C.PersonID
FOR XML AUTO;

Dies ist das Teilergebnis:

<SOH Name="Jon Yang" SalesOrderID="43793" />
<SOH Name="Eugene Huang" SalesOrderID="43767" />

Um die Elemente abzurufen, die <IndividualCustomer> das Name Attribut enthalten, das die Einzelnen Verkaufsauftragskopfinformationen als Unterelement enthält, wird die Abfrage mithilfe einer Unterauswahl neu geschrieben. 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.

In der Sales.Customer Tabelle werden einzelne Kundeninformationen gespeichert, einschließlich des PersonID Werts für diesen Kunden. Diese PersonID wird dann zum Suchen des Kontaktnamens in der Person.Person -Tabelle verwendet.

SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName AS Name, C.PersonID, C.CustomerID
      FROM Sales.Customer AS C, Person.Person AS P
      WHERE C.PersonID = P.BusinessEntityID) AS IndividualCustomer
LEFT OUTER JOIN  Sales.SalesOrderHeader AS 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>
...

Beispiel: Zurückgeben von Binärdaten

Diese Abfrage gibt ein Produktfoto aus der Tabelle ProductPhoto zurück. ThumbNailPhoto ist eine varbinary (max) -Spalte in der ProductPhoto -Tabelle. Der AUTO -Modus gibt standardmäßig einen Verweis (eine relative URL auf das virtuelle Stammverzeichnis der Datenbank, in der die Abfrage ausgeführt wird) auf die Binärdaten zurück. Das ProductPhotoID -Schlüsselattribut muss angegeben werden, damit das Bild später identifiziert werden kann. Beim Abrufen eines Bildverweises wie in diesem Beispiel dargestellt muss der Primärschlüssel der Tabelle auch in der SELECT Klausel angegeben werden, um eine Zeile eindeutig zu identifizieren.

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

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:

<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 (eine relative URL auf das virtuelle Stammverzeichnis der Datenbank, in der die Abfrage ausgeführt wurde) zurückgegeben. Dies tritt auf, wenn die OPTION BINARY BASE64 nicht angegeben ist.

Wenn der AUTO-Modus einen URL-Verweis auf die Binärdaten in Datenbanken zurückgibt, bei denen ein in der Abfrage angegebener Tabellen- oder Spaltenname nicht mit dem Tabellen- oder Spaltennamen in der Datenbank übereinstimmt, wird die Abfrage ausgeführt. Der in der Referenz zurückgegebene Fall ist jedoch 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" />

Dies kann ein Problem sein, insbesondere wenn dbobject Abfragen für eine Datenbank mit Groß-/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.

Beispiel: 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), ('#', 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 Ergebnis wird der Elementname <Special Chars> als <Special_x0020_Chars>. Der Attributname <Col#&2> wird als <Col_x0023__x0026_2>. Sowohl XML- als auch URL-Sonderzeichen werden codiert.

  • Wenn die Werte der Elemente oder Attribute eine der fünf standardmäßigen XML-Zeichenentitäten (', '', '", <und >&') enthalten, werden diese Speziellen XML-Zeichen immer mit XML-Zeichencodierung codiert. Im vorherigen Ergebnis wird der Wert & im Attributwert <Col1> als &codiert. Das #-Zeichen ist jedoch neu Standard s #, da es sich um ein gültiges XML-Zeichen und nicht um ein spezielles XML-Zeichen ist.

  • Wenn die Werte der Elemente oder Attribute spezielle URL-Zeichen enthalten, die in der URL eine besondere Bedeutung haben, werden sie nur im DBOBJECT-URL-Wert codiert und nur codiert, wenn das Sonderzeichen Teil eines Tabellen- oder Spaltennamens ist. Im Resultset wird das # -Zeichen, das Teil des Tabellennamens Col#&2 ist, als _x0023_ in the DBOJBECT URLcodiert.

Siehe auch