例: AUTO モードの使用

2017-06-13

次の例は、AUTO モードの使用方法を示しています。これらのクエリの多くは、AdventureWorks2012 サンプルデータベースの ProductModel テーブルの Instructions 列に格納されている自転車製造命令 XML ドキュメントに対して指定されます。

例: 顧客、注文、注文の詳細情報の取得

このクエリは、特定の顧客の顧客、注文、注文の詳細情報を取得します。

USE AdventureWorks2012;  
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;

クエリはテーブルの別名を識別、 Cust、 OrderHeader、 Detail、および Product するため、対応する要素は AUTO モードによって生成されます。ここでも、 SELECT 句で指定された列によってテーブルが識別される順序によって、これらの要素の階層が決まります。

次に結果の一部を示します。

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

例: GROUP BY 関数と集計関数の指定

次のクエリでは、個々の顧客 ID と、顧客が要求した注文の数が返されます。

USE AdventureWorks2012;  
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;This is the partial result:

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

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

...

例: AUTO モードでの計算列の指定

このクエリは、連結された個々の顧客名と注文情報を返します。計算列は、その時点で検出された最も内側のレベルに割り当てられるため、この例では <SOH> 要素です。連結された顧客名は、結果の <SOH> 要素の属性として追加されます。

USE AdventureWorks2012;  
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;

結果の一部を次に示します。

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

各販売注文ヘッダー情報をサブ要素として含むName属性を持つ<IndividualCustomer>要素を取得するには、サブ選択を使用してクエリを書き換えます。内部選択では、個々の顧客の名前を含む計算列を含む一時的な IndividualCustomer テーブルが作成されます。次に、このテーブルが SalesOrderHeader テーブルに結合され、結果が取得されます。

Sales.Customer テーブルには、その顧客のPersonID値を含む個々の顧客情報が格納されることに注意してください。この PersonID は、 Person.Person テーブルから連絡先名を検索するために使用されます。

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.CustomerIDFOR XML AUTO;

結果の一部を次に示します。

<IndividualCustomer Name="Jon Yang">

<SOH SalesOrderID="43793" />

<SOH SalesOrderID="51522" />

<SOH SalesOrderID="57418" />

</IndividualCustomer>

...

例: バイナリデータを返す

このクエリは、 ProductPhoto テーブルから製品の写真を返します。 ThumbNailPhotoは、ProductPhoto テーブルのvarbinary(max)列です。既定では、 AUTO モードでは、クエリが実行されるデータベースの仮想ルートへの相対 URL である参照がバイナリデータに返されます。イメージを識別するには、 ProductPhotoID キー属性を指定する必要があります。この例に示すようにイメージ参照を取得する場合、行を一意に識別するには、 SELECT 句でテーブルの主キーも指定する必要があります。

SELECT ProductPhotoID, ThumbNailPhoto  
FROM   Production.ProductPhoto   
WHERE ProductPhotoID=70  
FOR XML AUTO;

結果を次に示します。

-- result

<Production.ProductPhoto

ProductPhotoID="70"

ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />

BINARY BASE64 オプションを使用して、同じクエリが実行されます。このクエリは、base64 でエンコードされた形式でバイナリデータを返します。

SELECT ProductPhotoID, ThumbNailPhoto  
FROM   Production.ProductPhoto   
WHERE ProductPhotoID=70  
FOR XML AUTO, BINARY BASE64;

結果を次に示します。

-- result

<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />

既定では、AUTO モードを使用してバイナリデータを取得すると、バイナリデータではなく、クエリが実行されたデータベースの仮想ルートへの相対 URL への参照が返されます。これは、BINARY BASE64 オプションが指定されていない場合に発生します。

AUTO モードでは、クエリで指定されたテーブルまたは列名がデータベース内のテーブルまたは列名と一致しない、大文字と小文字を区別しないデータベースでバイナリデータへの URL 参照が返されると、クエリが実行されます。ただし、参照で返されるケースは一貫性がありません。例えば次が挙げられます。

SELECT ProductPhotoID, ThumbnailPhoto  
FROM   Production.ProductPhoto   
WHERE  ProductPhotoID=70  
FOR XML AUTO;

結果を次に示します。

<Production.PRODUCTPHOTO

PRODUCTPHOTOID="70"

THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />

これは、特に dbobject クエリが大文字と小文字を区別するデータベースに対して実行される場合に問題になる可能性があります。これを回避するには、クエリで指定されたテーブルまたは列の名前の大文字と小文字をデータベース内のテーブルまたは列の名前の大文字と小文字と一致させることが必要です。

例: エンコードについて

この例では、結果で発生するさまざまなエンコードを示します。

次のテーブルを作成します。

CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50));

次のデータをテーブルに追加します。

INSERT INTO [Special Chars] VALUES ('&', 0x20), ('#', 0x20);

このクエリは、テーブルからデータを返します。 FOR XML AUTO モードが指定されています。バイナリデータは参照として返されます。

SELECT * FROM [Special Chars] FOR XML AUTO;

結果を次に示します。

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

/>

これは、結果内の特殊文字をエンコードするプロセスです。

クエリ結果では、返される要素名と属性名の特殊な XML 文字と URL 文字は、対応する Unicode 文字の 16 進値を使用してエンコードされます。前の結果では、要素名 <Special Chars> は <Special_x0020_Chars>として返されます。属性名 <Col#&2> は <Col_x0023__x0026_2>として返されます。 XML と URL の両方の特殊文字がエンコードされます。
要素または属性の値に 5 つの標準 XML 文字エンティティ ('、""、 <、 >、および > のいずれかが含まれている場合、これらの特殊な XML 文字は常に XML 文字エンコードを使用してエンコードされます。前の結果では、属性<Col1>の値&値は&としてエンコードされます。ただし、#文字は有効な XML 文字であり、特殊な XML 文字ではないため、#のままです。
要素または属性の値に、URL に特別な意味を持つ特別な URL 文字が含まれている場合、それらは DBOBJECT URL 値でのみエンコードされ、特殊文字がテーブルまたは列名の一部である場合にのみエンコードされます。その結果、テーブル名Col#&2の一部である文字#は_x0023_ in the DBOJBECT URLとしてエンコードされます。

こちらもご覧ください

FOR XML で AUTO モードを使用する

次の方法で共有

例: AUTO モードの使用

例: 顧客、注文、注文の詳細情報の取得

例: GROUP BY 関数と集計関数の指定

例: AUTO モードでの計算列の指定

例: バイナリ データを返す

例: エンコードについて

こちらもご覧ください

その他のリソース

例: バイナリデータを返す