FOR XML での AUTO モードの使用

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

「FOR XML (SQL Server)」で説明しているように、AUTO モードを使用すると、入れ子構造の XML 要素としてクエリ結果が返されます。 AUTO モードでは、クエリ結果から生成される XML の構造はあまり制御されません。 AUTO モードのクエリは、単純な階層を生成する場合に役立ちます。 ただし、 FOR XML での EXPLICIT モードの使用 や FOR XML での 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> 要素のサブ要素として追加され、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 句にアスタリスク (*) ワイルドカード文字を指定すると、クエリ エンジンから行が返される順序に基づいて上記の説明と同じ方法で入れ子が決定されます。

次のステップ

次の記事では、AUTO モードの詳細について説明します。

関連項目