Microsoft SQL Server 2005 での FOR XML の新機能

Michael Rys
Microsoft Corporation

June 2004

対象 :
   Microsoft SQL Server 2005

概要 : これは、Michael Rys による連載記事の 1 回目です。今回は、次期バージョンの SQL Server のサーバー側 FOR XML 句の主な新機能について説明します。 これらの新機能により、アプリケーションでの XML サポートがさらに強化され、リレーショナル データから XML への、管理しやすいデータ集約機能を記述できるようになります。

目次

はじめに
SQL Server 2000 との下位互換性
XML データ型との統合
FOR XML の結果を代入する
FOR XML 式を入れ子にする
新しい PATH モード
FOR XML の拡張機能
まとめ

はじめに

FOR XML 句は、Microsoft SQL Server 2000 で初めて SELECT ステートメントに導入されました。 この句により、SELECT ステートメントから返されるリレーショナル行セットを XML に集約する機能が提供されます。 サーバー側の FOR XML では、RAW、AUTO、EXPLICIT という 3 つのモードがサポートされます。これらのモードは、次のようにそれぞれ異なる意味合いを持つ変換を行います。

RAW モードでは、行が返されるたびに row という名前の 1 つの要素が生成されます。

AUTO モードでは、レベルごとに 1 つの要素名を持つ簡単な階層が SELECT ステートメント内のデータの系列情報に基づいて帰納的に推測されます。

EXPLICIT モードでは、特定の行セット形式が必要になります。この行セット形式は、ほぼすべての XML イメージにマップできます。ただし、行セット形式は依然として 1 つの SQL クエリによって形成されます。

3 つのモードはすべて、大量のドキュメントを効率的に生成できるように、ストリーミング可能な手法で XML を生成するようにデザインされています。

EXPLICIT モード形式は、この目標の実現に大きな成功を収めています。 このモード形式でできないことはほんのわずかしかありません。任意の再帰部分リスト ツリーがその 1 つです。 しかしながら、行セット形式を生成するために使われる SQL 式は、非常に厄介な "最低のクエリ" になります。

残念なことに、SQL Server 2000 では FOR XML の結果はクライアント側でしか使用できないので、サーバー側では FOR XML EXPLICIT を使用する複雑な XML 構造を記述することが困難な作業になります。 私は行セット形式の設計者の 1 人として、数千行にも及ぶ EXPLICIT モード クエリを記述したり保守する人に尊敬の念を禁じ得ません。 また、使い勝手、管理面、複雑さといった問題についても理解しています。

SQL Server 2005 では、この "最低のクエリ" を記述する必要がなくなりました。 ここからは、SQL Server 2005 で FOR XML に追加された主な機能を見ていくことにしましょう。ここで触れる主な機能には、XML データ型との統合、式への代入と式を入れ子にすること、および新しい PATH モードがあります。 ここには Northwind スキーマ (英語) に基づいた例をいくつか用意してあります。これらの例により、新機能を使用して EXPLICIT モード クエリを簡単に記述する方法を示します。

FOR XML には、このような新機能以外に、ちょっとした新機能もいくつか追加されています。これらについては、このセクションの最後で簡単に説明しておきます。

注意   FOR XML は新しいバージョンでも行セットを集約する SQL SELECT ステートメントの句になります。そのため、ストアド プロシージャからの二次出力は変換できません。 ある形式の処理結果を XML に変換する場合は、ユーザー定義関数またはビューを使用してください。

SQL Server 2000 との下位互換性

SQL Server 2005 での FOR XML に関する重要な側面に、SQL Server 2000 での FOR XML との下位互換性の問題があります。 この下位互換性の問題には、不具合の修正、互換性モードの変更、FOR XML 動作の保持という 3 つのカテゴリがあります。

まず、不具合の修正というカテゴリでは、AUTO モードのビューとサブクエリの扱いに関するいくつかの不具合が修正されました。 また、空白文字の使い方を変更しないで、エンティティ化を最小限にするために、エンティティ化のルールも変更しました (この変更によって意味合いが異なってしまうことはありません)。 この 2 つは不具合の修正と見なされ、サーバーの互換性フラグの設定状態とは無関係に行われます。

多くのユーザーが、timestamp SQL 型のインスタンスは数値表記ではなく、(ベース 64 でエンコーディングされた) バイナリ値でレポートされるべきだと感じていました。 そこで、SQL Server 2005 では timestamp 値の扱いを変更し、ベース 64 でエンコーディングされたバイナリ値でシリアル化されるようにしました。 SQL Server 2000 と同じ動作を必要とする場合は、サーバーの互換性レベルを SQL Server 2000 に設定します。

最後に、SQL Server 2000 では、パフォーマンスの理由から、整形式かどうかのチェックを行わない、ストリーミング方式で結果が返されていました。 そのため、結果には、U+0007 (ASCII BELL 文字) などの無効な XML 文字や、(破損した !xmltext フィールドを使用する) 整形式ではない構造が含まれる可能性がありました。 通常、このようなケースはすべてクライアント側のパーサーによって検出されますが、ユーザーは結果のデータを利用するために XML パーサーを使うことができません。このようなケースでは代わりに簡単な部分文字列の抽出を使用します。 SQL Server 2005 では、下位互換性を保つためにこの動作を維持することになりました。 新しい XML データ型は無効な XML を許容しないので、SQL Server 2005 の標準の FOR XML クエリは結果を nvarchar(max) 型のインスタンスとして返します (ただし、XML データ型は FOR XML の結果のフラグメント形式を処理することはできます)。 もっと正確に言うと、新しい XML データ型は結果を 1 行で返します。つまり、セルに nvarchar(max) 型の文字列値が含まれている行セットを 1 つだけ返します。

XML データ型との統合

XML データ型の導入に伴い、XML のインスタンスを直接生成する機能を FOR XML に用意したいと考えました (つまり、FOR XML は 1 行、1 列の行セットを生成します。この場合、セルには XML データ型のインスタンスが含まれます)。

上記の下位互換性の考慮事項があったので、結果を XML として結果を生成するために、新しい TYPE ディレクティブを追加しました。 以下に例を示します。

SELECT * FROM Customers FOR XML AUTO, TYPE

上記のステートメントでは、Customers 要素が XML データ型インスタンスとして返されます。TYPE ディレクティブを指定しないと nvarchar(max) データ型のインスタンスとして返されます。

この結果は、XML データ型によって提供される、整形式の制約に準拠していることが保証されます。 結果が XML データ型インスタンスなので、XQuery 式を使用して結果をクエリし、再作成することもできます。 たとえば、次の式は Customer の連絡先名を新しい Person 要素に取得します。

SELECT (SELECT * FROM Customers FOR XML AUTO, TYPE).query(
'<doc>{
   for $c in /Customers
   return 
     <Person name="{data($c/@ContactName)}"/>
 }</doc>')

その結果、以下の内容が返されます (最初の要素のみを示しています)。

<doc>
  <Person name="Maria Anders" />
  <Person name="Ana Trujillo" />
  <Person name="Antonio Moreno" />
  ...
</doc>

FOR XML の結果を代入する

FOR XML クエリから代入可能な値が返されるようになったので、FOR XML クエリの結果を変数に代入したり、列に挿入することができます。

DECLARE @cust XML;
SET @cust = (SELECT * FROM Customers FOR XML AUTO, TYPE)
CREATE TABLE T(i int, x XML)
INSERT INTO T SELECT 1, (SELECT * FROM Customers FOR XML AUTO, TYPE)

FOR XML 式を入れ子にする

SQL Server 2005 の FOR XML は XML データ型の列を認識するので、XML データ型データ型をサブ要素として組み込みます。 したがって、FOR XML クエリを入れ子にして階層を生成できます。AUTO モードの帰納性に依存したり、EXPLICIT モードのクエリを記述する必要はありません。

それでは例を見てみましょう。 次の FOR XML EXPLICIT クエリは Customer の要素を返します。この要素には、注文内容と注文を処理した従業員が含まれます。 説明を簡単にするために、要素ごとに 1 つだけプロパティを返しています。

SELECT 1 as TAG,
       NULL as Parent,
       CustomerID as "Customer!1!CustomerID",
       NULL as "Order!2!OrderID",
       NULL as "Employee!3!LastName"
FROM Customers
UNION ALL
SELECT 2,
       1,
       Customers.CustomerID,
       Orders.OrderID,
       NULL
FROM Orders 
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
UNION ALL
SELECT DISTINCT 3,
       1,
       Customers.CustomerID,
       NULL,
       Employees.LastName
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY "Customer!1!CustomerID","Employee!3!LastName","Order!2!OrderID"
FOR XML EXPLICIT

この結果として、以下の内容が返されます (最初の顧客のみを示しています)。

<Customer CustomerID="ALFKI">
  <Order OrderID="10643" />
  <Order OrderID="10692" />
  <Order OrderID="10702" />
  <Order OrderID="10835" />
  <Order OrderID="10952" />
  <Order OrderID="11011" />
  <Employee LastName="Davolio" />
  <Employee LastName="Leverling" />
  <Employee LastName="Peacock" />
  <Employee LastName="Suyama" />
</Customer>
...

おわかりのように、要素ごとに 1 つの SELECT ステートメントが必要です。 また、order by 句によって各子要素がその親要素でグループ化されるように、すべての子要素で親要素の ID を繰り返しています。 行セットから XML へのストリーミング方式のシリアル化は、このグループ化に依存して、正しく入れ子になるようにしています。

ここで、FOR XML 式を入れ子にすることで、上記のステートメントを書き換える方法を見てみましょう。 新しい TYPE ディレクティブを利用して XML データ型インスタンスを生成し (別の FOR XML クエリに埋め込まれる場合は、エンティティ化されるテキスト結果を取得し)、副選択を入れ子にして階層を定義できます。

3 つの "エンティティ"要素ごとに個別の FOR XML クエリを使用し、それらのクエリを入れ子にして階層を表します。 以下は、上記の EXPLICIT モードのクエリを AUTO モードを使用して入れ子にした状態に書き換えたものです。

SELECT CustomerID as "CustomerID",
      (SELECT OrderID as "OrderID"
       FROM Orders "Order"
       WHERE "Order".CustomerID = Customer.CustomerID
       FOR XML AUTO, TYPE),
      (SELECT DISTINCT LastName as "LastName"
       FROM Employees Employee
       JOIN Orders "Order" ON "Order".EmployeeID = Employee.EmployeeID
       WHERE Customer.CustomerID = "Order".CustomerID
       FOR XML AUTO, TYPE)
FROM Customers Customer
FOR XML AUTO, TYPE

上記のステートメントからは、要素の並び順が保証されることを除いては、EXPLICIT モードのクエリと同じ結果が返されます (順序を並べ替える場合は、ORDER BY ステートメントを追加できます)。

このクエリは同じ数の SELECT ステートメントと結合条件を含んでいますが、簡単に記述、理解、管理できることは明らかです。

新しい PATH モード

上記のセクションでは、FOR XML 機能の一部を使用して、簡単な FOR XML EXPLICIT クエリを、FOR XML、AUTO モード、新しい XML データ型の入れ子を利用した単純なクエリに書き換える方法を理解しました。

ただし、上記のクエリは単純すぎて FOR XML EXPLICIT を使用した実際のシナリオを反映できていない言われるかもしれません。

たとえば、EXPLICIT モードの優れた機能には、属性と要素を自由に混在させたり、ラッパーと入れ子になった複雑なプロパティを作成したり、空白区切りの値リストや混合コンテンツを作成したりすることなどがあります。 これらの結果はいずれも、FOR XML AUTO クエリを入れ子にすることでは実現できません。 そうすると、このような機能に対応するためには、相変わらず EXPLICIT モードのクエリを記述することになるのでしょうか。

諦めないでください! これと同様の柔軟性を備え、はるかに簡単な方法でこのような機能を提供する、新しい FOR XML モードがあります。 新しく用意された PATH モードを、FOR XML 式を入れ子にする機能を併用することで、複雑な XML ドキュメントを簡単に生成できます。

PATH モードでは、XPath に似た構文を列名として使用できます。列名は後から属性 ("@a" など)、要素 ("e" など)、サブ要素構造 ("e1/e2")、要素コンテンツ ("*")、テキスト ノード ("text()")、またはデータ値 ("data()") にマップされます。 RAW モードと同様に、行要素の既定の名前は row になり、NCName (プレフィックスなしの名前) で上書きできます。

いくつかの例を見てみましょう。 まずは、上記の EXPLICT モードのクエリを PATH モードの形式に書き換えたものをご覧ください。

SELECT CustomerID as "@CustomerID",
           (SELECT OrderID as "@OrderID"
            FROM Orders
            WHERE Orders.CustomerID = Customers.CustomerID
            FOR XML PATH('Order'), TYPE),
          (SELECT DISTINCT LastName as "@LastName"
           FROM Employees
           JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
           WHERE Customers.CustomerID = Orders.CustomerID
           FOR XML PATH('Employee'), TYPE)
FROM Customers
FOR XML PATH('Customer')

この例は AUTO モードの形式に似ており、同じ結果が返されます。

では、PATH モード固有の機能をいくつか見てみましょう。 次のクエリは顧客情報を取り出し、列の別名としてより複雑なパス式を使用して、住所と連絡先情報を個別のサブ要素にグループ化します。その後、新しい ROOT ディレクティブを使用して要素を囲むルート ノードを追加します。

SELECT CustomerID as "@CustomerID", 
           CompanyName,
           Address as "address/street",
           City as "address/city",
           Region as "address/region",
           PostalCode as "address/zip",
           Country as "address/country",
           ContactName as "contact/name",
           ContactTitle as "contact/title",
           Phone as "contact/phone", 
           Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer'), ROOT('doc')

このクエリからは次のドキュメントが返されます (最初の顧客要素のみを示しています)。

<doc>
  <Customer CustomerID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <address>
      <street>Obere Str. 57</street>
      <city>Berlin</city>
      <zip>12209</zip>
      <country>Germany</country>
    </address>
    <contact>
      <name>Maria Anders</name>
      <title>Sales Representative</title>
      <phone>030-0074321</phone>
      <fax>030-0076545</fax>
    </contact>
  </Customer>
  ...
</doc>

EXPLICIT モードを使用すると、このクエリはどのような形式になるでしょうか。 1 つの選択ではなく、4 つの SELECT 句 (リーフ以外の要素ごとに 1 つ) が必要になります。

SELECT top 1 
       1 as TAG,
       NULL as Parent,
       1 as "doc!1!dummy!hide",
       NULL as "Customer!2!CustomerID",
       NULL as "Customer!2!CompanyName!element",
       NULL as "address!3!street!element",
       NULL as "address!3!city!element",
       NULL as "address!3!region!element",
       NULL as "address!3!zip!element",
       NULL as "address!3!country!element",
       NULL as "contact!4!name!element",
       NULL as "contact!4!title!element",
       NULL as "contact!4!phone!element",
       NULL as "contact!4!fax!element"
FROM Customers
UNION ALL
SELECT 2, 1,
       1,
       CustomerID, CompanyName,
       NULL, NULL, NULL, NULL, NULL,
       NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 3, 2,
       1,
       CustomerID, NULL,
       Address, City, Region, PostalCode, Country,
       NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 4, 2,
       1,
       CustomerID, NULL,
       NULL, NULL, NULL, NULL, NULL,
       ContactName, ContactTitle, Phone, Fax
FROM Customers
ORDER BY "doc!1!dummy!hide","Customer!2!CustomerID"
FOR XML EXPLICIT, TYPE

これで、EXPLICIT モードが "最低のクエリ" と呼ばれる場合がある理由がおわかりになるでしょう。

大事なことを言い忘れていました。以下に値リストを生成する例を示します。さらに、テキスト ノードの用途を示します。

select CustomerID as "@ID",
      (select OrderID as "data()"
       from Orders
       where Customers.CustomerID=Orders.CustomerID
       FOR XML PATH('')
      ) as "@OrderIDs",
       CompanyName,
       ContactTitle as "ContactName/@ContactTitle",
       ContactName as "ContactName/text()",
       PostalCode as "Address/@ZIP",
       Address as "Address/Street",
       City as "Address/City"
FROM Customers
FOR XML PATH('Customer')

次の形式の結果が作成されます (1 つの顧客を例に示しています)。

<Customer ID="HUNGC" OrderIDs="10375 10394 10415 10600 10660">
  <CompanyName>Hungry Coyote Import Store</CompanyName>
  <ContactName 
     ContactTitle="Sales Representative">Yoshi Latimer</ContactName>
  <Address ZIP="97827">
    <Street>City Center Plaza 516 Main St.</Street>
    <City>Elgin</City>
  </Address>
</Customer>

クエリの関連部分を細かく調べてみましょう。

サブクエリでは OrderIDs 属性リストを生成し、OrderID 列の値をアトミック値として (パス data() を使用して) マップします。 次に、マップされた値は、兄弟のアトミック値間に空白を追加することでテキスト ノードとしてシリアル化されます。これらの値は、行セット内の次のセルに設定されます。 その後、長さ 0 の文字列を PATH モードの引数として使用することで行の名前を生成しないようにし、FOR XML PATH 式の結果として 1 つの文字列を取得します (TYPE ディレクティブが指定されていないことに注意してください)。 取得した文字列は、FOR XML 式によって OrderIDs 属性にマップされます。

CompanyName は、同じ名前のサブ要素にマップされます。

ContactTitle は ContactName 要素の ContactTitle 属性を生成し、ContactName 列の値は同じ要素のテキスト ノードにマップされます。 この場合、ContactName を ContactName 要素に直接マップすることで、同じ結果が得られることに注意してください。

最後に、Address 要素部分のプロパティが組み合わせられます。

FOR XML の拡張機能

SQL Server 2005 には、上記の新機能のほか、次の新機能が用意されています。

1. RAW モードを ELEMENTS ディレクティブと組み合わせて、行要素名を上書きするパラメータを受け取ることができます。 以下に例を示します。

   SELECT *
   FROM Customers 
   FOR XML RAW('Customer'), ELEMENTS
   

   結果は次のとおりです (最初の顧客のみを示しています)。

   <Customer>
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
     <ContactName>Maria Anders</ContactName>
     <ContactTitle>Sales Representative</ContactTitle>
     <Address>Obere Str. 57</Address>
     <City>Berlin</City>
     <PostalCode>12209</PostalCode>
     <Country>Germany</Country>
     <Phone>030-0074321</Phone>
     <Fax>030-0076545</Fax>
   </Customer>
   

2. ELEMENTS ディレクティブには、NULL 値を xsi:nil="true" という属性を持つ要素にマップする XSINIL オプションが用意されています。 以下に例を示します。

   SELECT * 
   FROM Customers Customer
   WHERE Region is null 
   FOR XML AUTO, ELEMENTS XSINIL
   

   結果は次のとおりです (最初の顧客のみを示しています)。

   <Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <CustomerID>ALFKI</CustomerID>
     <CompanyName>Alfreds Futterkiste</CompanyName>
     <ContactName>Maria Anders</ContactName>
     <ContactTitle>Sales Representative</ContactTitle>
     <Address>Obere Str. 57</Address>
     <City>Berlin</City>
     <Region xsi:nil="true" />
     <PostalCode>12209</PostalCode>
     <Country>Germany</Country>
     <Phone>030-0074321</Phone>
     <Fax>030-0076545</Fax>
   </Customer>
   

3. 新しいインライン スキーマ推測ディレクティブ XMLSCHEMA が、RAW モードと AUTO モードに追加されました。このディレクティブは、対象の名前空間 URI を省略可能な引数として受け取ります。 以下に例を示します。

   SELECT *
   FROM Customers 
   FOR XML RAW('Customer'), XMLSCHEMA('urn:example.com')
   

   結果は次のとおりです (スキーマとデータの一部だけを示しています)。

   <xsd:schema targetNamespace="urn:example.com" 
      xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:sqltypes=
        "https://schemas.microsoft.com/sqlserver/2004/sqltypes" 
      elementFormDefault="qualified">
     <xsd:import namespace=
           "https://schemas.microsoft.com/sqlserver/2004/sqltypes" />
     <xsd:element name="Customer">
       ...
     </xsd:element>
   </xsd:schema>
   <Customer xmlns="urn:example.com" CustomerID="ALFKI" 
   CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" 
   ContactTitle="Sales Representative" Address="Obere Str. 57" 
   City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" 
   Fax="030-0076545" />
   ...
   

まとめ

SQL Server 2005 で拡張された FOR XML サポートの概要を説明しました。 追加された機能は、ほとんどが新しい XML データ型によって実現されます。追加機能を使用すると、リレーショナル データから XML を生成するための FOR XML が非常に優れた使いやすいツールになります。 FOR XML クエリを入れ子にする機能と併用する新しい PATH モードには、EXPLICIT モードのクエリの大部分を、単純で管理しやすい方法に置き換えるための十分な能力が備わっています。

今でも (CDATA セクションの生成や !xmltext ディレクティブの使用のために) EXPLICIT モードを使用する場合もありますが、新機能により "最低のクエリ" を使用する機会はかなり減るはずです。

ここで、最後の質問です。この機能にアクセスするにはどうすればいいのでしょうか。

SQL Server 2005 Beta 2 は指名されたユーザーやパートナー、MSDN ユニバーサルと MSDN エンタープライズの会員、および Beta 1 の参加者に配布される予定です。

MSDN ユニバーサルまたは MSDN エンタープライズの会員の方は、SQL Server 2005 Beta 2 がリリースされたときに、MSDN サブスクライバ ダウンロード サイトからダウンロードできるようになります。

会員でない方は、「SQL Server Beta Nominations」ページ (英語) に注目していてください。 ベータ版が使用可能になったときに、このページに情報が記載されます。