次の方法で共有

  • [アーティクル]

入れ子になった FOR XML クエリ

SQL Server 2000 では、SELECT クエリの最上位レベルにしか FOR XML 句を指定できません。結果の XML は、主にクライアントに返されてから追加の処理が行われます。一方、SQL Server 2005 以降では、xml データ型と FOR XML クエリの TYPE ディレクティブにより、FOR XML クエリによって返された XML にサーバー側でさらに処理を加えることができます。

xml 型の変数を使用した処理

FOR XML クエリの結果を xml 型の変数に代入できます。また、XQuery を使用して結果にクエリを実行し、その結果を xml 型の変数に代入してからさらに処理を加えることができます。

DECLARE @x xml
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 or ProductModelID=119
        FOR XML RAW, TYPE)
SELECT @x
-- Result
--<row ProductModelID="122" Name="All-Purpose Bike Stand" />
--<row ProductModelID="119" Name="Bike Wash" />

いずれかの xml データ型メソッドを使用して、変数 @x に返された XML にさらに処理を加えることができます。たとえば、value() メソッドを使用して、ProductModelID 属性の値を取得できます。

DECLARE @i int
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'))
SELECT @i

次の例では、FOR XML 句に TYPE ディレクティブが指定されているので、FOR XML クエリの結果が xml 型で返されます。

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 or ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot')

次に結果を示します。

<myRoot>
  <row ProductModelID="122" Name="All-Purpose Bike Stand" />
  <row ProductModelID="119" Name="Bike Wash" />
</myRoot>

結果は xml 型なので、次のクエリのように、この XML に対していずれかの xml データ型メソッドを直接指定することができます。クエリでは、query() メソッド (xml データ型) が使用され、<myRoot> 要素の最初の <row> 子要素が取得されます。

SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 or ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]')

結果を次に示します。

<row ProductModelID="122" Name="All-Purpose Bike Stand" />

内側の FOR XML クエリの結果を外側のクエリに xml 型インスタンスとして返す

入れ子構造の FOR XML クエリを記述して、内側のクエリの結果を xml 型で外側のクエリに返すことができます。次に例を示します。

SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE

上のクエリに関して、次の点に注意してください。

  • 内側の FOR XML クエリで生成された XML は、外側の FOR XML で生成された XML に追加されます。

  • 内側のクエリでは、TYPE ディレクティブが指定されています。このため、内側のクエリから返される XML データは、xml 型になります。TYPE ディレクティブを指定しなかった場合、内側の FOR XML クエリの結果は nvarchar(max) 型として返され、XML データはエンティティ化されます。

結果の XML データ構造の制御

入れ子構造の FOR XML クエリを使用すると、結果の XML データ構造の定義を制御しやすくなります。SQL Server 2000 では、既定で RAW および AUTO モードのクエリで属性中心の XML が生成されます。次に例を示します。

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW

属性中心の結果を次に示します。

<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

ELEMENTS ディレクティブを指定することにより、すべての XML を要素中心として取得することもできます。次に例を示します。

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS

要素中心の結果を次に示します。

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

したがって、SQL Server 2000 では、クエリの結果として属性中心か要素中心のいずれかの XML を選択する必要があります。一方、SQL Server 2005 以降では、入れ子構造の FOR XML クエリを使用して、一部が属性中心で、一部が要素中心の XML を作成できます。

入れ子構造の FOR XML クエリを使用して属性中心と要素中心の両方の XML を指定する方法の詳細については、「FOR XML クエリと入れ子になった FOR XML クエリの比較」および「入れ子になった FOR XML クエリを使用した XML の構造化」を参照してください。

SQL Server 2000 では、EXPLICIT モードを使用してクエリを作成する方法でのみ兄弟を作成できます。ただし、この方法は複雑になる場合があります。SQL Server 2005 以降では、入れ子構造で AUTO モードの FOR XML クエリを指定することで、兄弟を含む XML 階層を生成できます。詳細については、「入れ子になった AUTO モードのクエリを使用した兄弟の生成」を参照してください。

使用するモードに関係なく、入れ子構造の FOR XML クエリを使用すると、結果の XML の構造の定義を制御しやすくなります。入れ子構造の FOR XML クエリは、EXPLICIT モードのクエリの代わりに使用できます。

例

ここでは、入れ子になった FOR XML クエリの例を紹介します。