AUTO モードの使用

[アーティクル]
12/16/2008

「FOR XML を使用した XML の構築」で説明したように、AUTO モードを使用すると、入れ子構造の XML 要素としてクエリ結果が返されます。AUTO モードでは、クエリ結果から生成される XML の構造はあまり制御されません。AUTO モードのクエリは、単純な階層を生成する場合に役立ちます。ただし、EXPLICIT モードの使用や PATH モードの使用により、クエリ結果から XML の構造を決定するときに、より厳密な制御や高い柔軟性を実現できます。

FROM 句の各テーブルは XML 要素として表され、そのうち少なくとも 1 列が SELECT 句のリストに含められます。FOR XML 句で省略可能な ELEMENTS オプションを指定すると、SELECT 句のリストに含められる列は属性またはサブ要素にマップされます。

生成される XML 内の要素が入れ子になった XML 階層は、SELECT 句で指定されている列で識別されるテーブルの順序に基づきます。そのため、SELECT 句で列名を指定する順序は重要です。先頭、つまり識別された左端のテーブルにより、生成される XML ドキュメント内の最上位要素が形成されます。SELECT ステートメント内の列で識別された左から 2 番目のテーブルにより、最上位要素内のサブ要素が形成されます。以降についても同様です。

SELECT 句のリストに含まれている列名が、SELECT 句でそれ以前に指定した列によって既に識別されたテーブルに含まれている場合、その列は、階層の新しいレベルを開くのではなく、既に作成されている要素の属性として追加されます。ELEMENTS オプションを指定している場合は、その列が属性として追加されます。

たとえば、次のクエリを実行するとします。

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

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

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

SELECT 句では、次の点に注意してください。

CustomerID は Cust テーブルを参照します。そのため、<Cust> 要素が作成され、CustomerID がその要素の属性として追加されます。
次に、OrderHeader.CustomerID、OrderHeader.SaleOrderID、および OrderHeader.Status の 3 列が、OrderHeader テーブルを参照します。そのため、<OrderHeader> 要素が <Cust> 要素のサブ要素として追加され、OrderHeader テーブルを参照する 3 列が <OrderHeader> の属性として追加されます。
次に、Cust.CustomerType 列が、Cust.CustomerID 列で既に識別された Cust テーブルを再び参照します。このため、新しい要素は作成されません。この場合、以前作成した <Cust> 要素に CustomerType 属性が追加されます。
クエリでは、テーブル名の別名が指定されます。これらの別名は、対応する要素名として示されます。
1 つの親のすべての子をグループ化するには、ORDER BY を指定する必要があります。

次のクエリは、SELECT 句で OrderHeader テーブルの列が指定されてから Cust テーブルの列が指定されている点を除いて、上記のクエリと同様です。そのため、最初の <OrderHeader> 要素が作成されてから、その要素に <Cust> 子要素が追加されています。

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

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

<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
  <Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...

FOR XML 句に ELEMENTS オプションを追加すると、要素中心の XML が返されます。

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

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

<Cust>
  <CustomerID>1</CustomerID>
  <CustomerType>S</CustomerType>
  <OrderHeader>
    <CustomerID>1</CustomerID>
    <SalesOrderID>43860</SalesOrderID>
    <Status>5</Status>
  </OrderHeader>
   ...
</Cust>
...

このクエリでは、<Cust> 要素の作成時に CustomerID の値が行ごとに比較されます。これは、CustomerID がテーブルの主キーであるためです。CustomerID がテーブルの主キーとして識別されない場合、列のすべての値 (このクエリでは CustomerID、CustomerType) が行ごとに比較されます。値が異なる場合は、新しい <Cust> 要素が XML に追加されます。

これらの列の値を比較するとき、比較するいずれかの列が text 型、ntext 型、image 型、または xml 型の場合は、FOR XML では値が同じ場合でも値が異なると想定され比較されません。これは、ラージオブジェクトの比較がサポートされていないためです。選択した行ごとに、要素が結果に追加されます。(n)varchar(max) 型および varbinary(max) 型の列は比較されることに注意してください。

集計列や計算列の場合と同様に、SELECT 句内の列を FROM 句内で識別されるいずれかのテーブルと関連付けることができない場合、その列がリストに見つかった時点で最も深い入れ子レベルの XML ドキュメントに追加されます。このような列が SELECT 句内の最初の列の場合は、最上位の要素に追加されます。

SELECT 句にアスタリスク (*) ワイルドカード文字を指定すると、クエリエンジンから行が返される順序に基づいて上記の説明と同じ方法で入れ子が決定されます。

クエリに BINARY BASE64 オプションを指定すると、バイナリデータが base64 エンコード形式で返されます。AUTO モードでは、BINARY BASE64 オプションを指定しないと、既定でバイナリデータの URL エンコードがサポートされます。つまり、バイナリデータではなく、クエリが実行されたデータベースの仮想ルートからの相対 URL への参照が返されます。この参照は、それ以降の操作で SQLXML ISAPI dbobject クエリを使用して実際のバイナリデータにアクセスするときに使用できます。クエリで画像を識別するには、主キー列など、十分な情報を提供する必要があります。

クエリを指定するときに、ビューのバイナリ列に別名を使用すると、その別名がバイナリデータの URL エンコードで返されます。それ以降の操作では別名は無意味になり、URL エンコードを使用して画像を取得することはできません。したがって、FOR XML AUTO モードを使用してビューのクエリを実行するときは、別名を使用しないでください。

返される XML を細分化する際の AUTO モードヒューリスティックについて

AUTO モードでは、返される XML の構造はクエリに基づいて決定されます。要素を入れ子にする方法を決定するとき、AUTO モードヒューリスティックによって、隣接する行の列値が比較されます。ntext 型、text 型、image 型、および xml 型を除くすべての型の列が比較されます。(n)varchar(max) 型と varbinary(max) 型の列は比較されます。

次の例では、生成される XML の構造を決定する AUTO モードヒューリスティックを示します。

SELECT T1.Id, T2.Id, T1.Name
FROM   T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id

テーブル T1 のキーが指定されていない場合は、新しい <T1> 要素の開始位置を決定するために、ntext 型、text 型、image 型、および xml 型を除く T1 の列のすべての値が比較されます。次に、Name 列が nvarchar(40) 型で、SELECT ステートメントから次の行セットが返されるとします。

T1.Id  T1.Name  T2.Id
-----------------------
1       Andrew    2
1       Andrew    3
1       Nancy     4

AUTO モードヒューリスティックにより、テーブル T1 のすべての値 (Id 列と Name 列) が比較されます。最初の 2 行の Id 列と Name 列の値は同じなので、2 つの <T2> 子要素を持っている 1 つの <T1> 要素が結果に追加されます。

次に、返される XML を示します。

<T1 Id="1" Name="Andrew">
    <T2 Id="2" />
    <T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
      <T2 Id="4" />
</T>

ここで、Name 列は text 型であるとします。この型の値は、AUTO モードヒューリスティックによって比較されません。この場合、値が異なると想定されます。この結果、次に示すように XML が生成されます。

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

例

次の例では、AUTO モードの使用方法を示します。これらのクエリの多くは、ProductModel テーブルの Instructions 列に格納されている、自転車製造手順の XML ドキュメントに対して指定されています。XML 命令の詳細については、「AdventureWorks データベースの xml データ型表現」を参照してください。

A. 顧客情報、注文情報、および注文明細情報の取得

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

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

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

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

<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. GROUP BY と集計関数の指定

次のクエリにより、各顧客の ID と顧客が要求した注文数が返されます。

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

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

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

C. AUTO モードでの計算列の指定

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

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

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

<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />

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

Sales.Individual テーブルには、顧客の ContactID 値など、各顧客の情報が格納されていることに注意してください。次に、ContactID を使用して Person.Contact テーブルから連絡担当者名を見つけます。

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

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

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

D. バイナリデータを返す

次のクエリでは、Employees テーブルから従業員の写真が返されます。Photo は、Employees テーブル内の image 列です。既定では、AUTO モードにより、バイナリデータへの参照、つまりクエリが実行されるデータベースの仮想ルートからの相対 URL が返されます。画像を識別するには、EmployeeID キー属性を指定する必要があります。この例で示すように、画像の参照を取得する場合、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 オプションを指定しないと、相対 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 クエリが実行されるときに問題になる可能性があります。この問題を回避するには、クエリで指定するテーブル名や列名での大文字と小文字の区別の方法が、データベース内のテーブル名や列名での大文字と小文字の区別の方法と一致している必要があります。

E. エンコードについて

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

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

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

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

INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 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="&amp;" 
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&amp;']/@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 の一部である文字 # は、DBOJBECT URL で _x0023_ とエンコードされます。

次の方法で共有

AUTO モードの使用

返される XML を細分化する際の AUTO モードヒューリスティックについて

例

A. 顧客情報、注文情報、および注文明細情報の取得

B. GROUP BY と集計関数の指定

C. AUTO モードでの計算列の指定

D. バイナリデータを返す

E. エンコードについて

参照

関連項目

その他の技術情報

ヘルプおよび情報

その他のリソース

次の方法で共有

AUTO モードの使用

返される XML を細分化する際の AUTO モード ヒューリスティックについて

例

A. 顧客情報、注文情報、および注文明細情報の取得

B. GROUP BY と集計関数の指定

C. AUTO モードでの計算列の指定

D. バイナリ データを返す

E. エンコードについて

参照

関連項目

その他の技術情報

ヘルプおよび情報

その他のリソース

返される XML を細分化する際の AUTO モードヒューリスティックについて

D. バイナリデータを返す