例:AUTO モードの使用

[アーティクル]
08/28/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

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

例: 顧客情報、注文情報、および注文明細情報を取得する

このクエリでは、特定の顧客の顧客情報、注文情報、および注文明細情報を取得しています。

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;

クエリでは 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 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;

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

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

例: AUTO モードで計算列を指定する

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

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;

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

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

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

<IndividualCustomer Name="Jon Yang">
  <SOH SalesOrderID="43793" />
  <SOH SalesOrderID="51522" />
  <SOH SalesOrderID="57418" />
</IndividualCustomer>
...

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

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

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

結果を次に示します。

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

結果を次に示します。

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

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

大文字と小文字が区別されないデータベースでは、クエリで指定したテーブル名や列名がデータベース内のテーブル名や列名と一致しない場合でも、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としてエンコードされます。

次の方法で共有

例:AUTO モードの使用

例: 顧客情報、注文情報、および注文明細情報を取得する

例: GROUP BY と集計関数を指定する

例: AUTO モードで計算列を指定する

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

例: エンコードを理解する

関連項目

フィードバック

その他のリソース

次の方法で共有

例:AUTO モードの使用

例: 顧客情報、注文情報、および注文明細情報を取得する

例: GROUP BY と集計関数を指定する

例: AUTO モードで計算列を指定する

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

例: エンコードを理解する

関連項目

フィードバック

その他のリソース

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