Beispiele: Verwenden des AUTO-Modus

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

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.

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

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

Beispiel: 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 (eine relative 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 (eine relative 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.

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)
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="&amp;" 
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&amp;']/@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 &amp; 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 der 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