[アーティクル]
04/01/2012

sql:max-depth を使用した、再帰リレーションシップの深さの指定

リレーショナルデータベースでは、テーブルのリレーションシップにそのテーブル自身が含まれることを、再帰リレーションシップと呼びます。たとえば、監督者と被監督者のリレーションシップでは、従業員の記録を格納するテーブルのリレーションシップに、そのテーブル自身が含まれます。この場合、従業員テーブルはリレーションシップの 1 つの側では監督者となり、別の側では被監督者となります。

マッピングスキーマには、要素とその先祖が同じ型の再帰リレーションシップを含めることができます。

例 A

次のテーブルを考えてみます。

Emp (EmployeeID, FirstName, LastName, ReportsTo)

このテーブルでは、ReportsTo 列にマネージャの従業員 ID が格納されています。

ここで従業員の XML 階層を生成して、次のサンプル XML フラグメントに示すように、マネージャである従業員を階層の一番上に、マネージャに報告を行う従業員をそれに対応する階層に表示したいとします。このフラグメントの内容は、従業員 1 に対する再帰ツリーとなります。

<?xml version="1.0" encoding="utf-8" ?> 
<root>
  <Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">
     <Emp FirstName="Andrew" EmployeeID="2" LastName="Fuller" /> 
     <Emp FirstName="Janet" EmployeeID="3" LastName="Leverling">
        <Emp FirstName="Margaret" EmployeeID="4" LastName="Peacock">
          <Emp FirstName="Steven" EmployeeID="5" LastName="Devolio">
...
...
</root>

このフラグメントでは、従業員 5 が従業員 4、従業員 4 が従業員 3 に、従業員 3 と 2 は従業員 1 に報告を行います。

この結果を生成するには、次の XSD スキーマを使用して、これに対する XPath クエリを指定できます。このスキーマでは、EmployeeType 型の <Emp> 要素が記述されています。この要素は、同じ EmployeeType 型の子要素 <Emp> で構成されています。これは、要素とその先祖が同じ型の再帰リレーションシップです。さらに、このスキーマでは <sql:relationship> により、監督者と被監督者間の親子リレーションシップが記述されています。この <sql:relationship> では、Emp は親と子の両方のテーブルとして出現します。

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="SupervisorSupervisee"
                                  parent="Emp"
                                  parent-key="EmployeeID"
                                  child="Emp"
                                  child-key="ReportsTo" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="Emp" type="EmployeeType" 
                          sql:relation="Emp" 
                          sql:key-fields="EmployeeID" 
                          sql:limit-field="ReportsTo" />
  <xsd:complexType name="EmployeeType">
    <xsd:sequence>
      <xsd:element name="Emp" type="EmployeeType" 
                              sql:relation="Emp" 
                              sql:key-fields="EmployeeID"
                              sql:relationship="SupervisorSupervisee"
                              sql:max-depth="6" />
    </xsd:sequence> 
    <xsd:attribute name="EmployeeID" type="xsd:ID" />
    <xsd:attribute name="FirstName" type="xsd:string"/>
    <xsd:attribute name="LastName" type="xsd:string"/>
  </xsd:complexType>
</xsd:schema>

このリレーションシップは再帰的なので、何らかの方法でスキーマ内の再帰の深さを指定する必要があります。指定しない場合、結果は無限再帰となり、従業員から次の従業員へと無限に報告を行うことになります。sql:max-depth 注釈を使用すと、再帰の深さを指定できます。この例の場合、sql:max-depth の値を指定するには、その企業の管理階層の深さを知っておく必要があります。

注
このスキーマでは sql:limit-field 注釈が指定されていますが、sql:limit-value 注釈は指定されていません。これにより、結果として生成される階層の一番上のノードは、だれにも報告しない従業員だけになります (ReportsTo は NULL です)。ここでは sql:limit-field 注釈を指定し、sql:limit-value 注釈を指定しない (既定で NULL に設定される) ことにより、このような階層が実現されます。結果の XML に、可能な報告ツリー (テーブル内の各従業員の報告ツリー) をすべて含める場合は、スキーマから sql:limit-field 注釈を削除します。

このスキーマでは sql:limit-field 注釈が指定されていますが、sql:limit-value 注釈は指定されていません。これにより、結果として生成される階層の一番上のノードは、だれにも報告しない従業員だけになります (ReportsTo は NULL です)。ここでは sql:limit-field 注釈を指定し、sql:limit-value 注釈を指定しない (既定で NULL に設定される) ことにより、このような階層が実現されます。結果の XML に、可能な報告ツリー (テーブル内の各従業員の報告ツリー) をすべて含める場合は、スキーマから sql:limit-field 注釈を削除します。

注
次の手順では、tempdb データベースを使用します。

スキーマに対してサンプル XPath クエリをテストするには

仮想ルートが示す tempdb データベースに、Emp という名前のサンプルテーブルを作成します。

USE tempdb
CREATE TABLE Emp (
       EmployeeID int primary key, 
       FirstName  varchar(20), 
       LastName   varchar(20), 
       ReportsTo int)

次のサンプルデータを追加します。

INSERT INTO Emp values (1, 'Nancy', 'Devolio',NULL)
INSERT INTO Emp values (2, 'Andrew', 'Fuller',1)
INSERT INTO Emp values (3, 'Janet', 'Leverling',1)
INSERT INTO Emp values (4, 'Margaret', 'Peacock',3)
INSERT INTO Emp values (5, 'Steven', 'Devolio',4)
INSERT INTO Emp values (6, 'Nancy', 'Buchanan',5)
INSERT INTO Emp values (7, 'Michael', 'Suyama',6)

上のスキーマのコードをコピーして、テキストファイルに貼り付け、maxDepth.xml として保存します。
次のテンプレートをコピーして、テキストファイルに貼り付け、maxDepth.xml を保存したディレクトリに maxDepthT.xml として保存します。このテンプレートのクエリでは、Emp テーブル内の全従業員が返されます。
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="maxDepth.xml">
    /Emp
  </sql:xpath-query>
</ROOT>
```
マッピングスキーマ (maxDepth.xml) に指定するディレクトリパスは、テンプレートを保存するディレクトリに対する相対パスです。次のように、絶対パスを指定することもできます。
```
mapping-schema="C:\MyDir\maxDepth.xml"
```
SQLXML 4.0 テストスクリプト (sqlxml4test.vbs) を作成し、それを使用してテンプレートを実行します。詳細については、「ADO を使用した、SQLXML 4.0 クエリの実行」を参照してください。

次に結果セットを示します。

<?xml version="1.0" encoding="utf-8" ?> 
<root>
  <Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">
  <Emp FirstName="Andrew" EmployeeID="2" LastName="Fuller" /> 
    <Emp FirstName="Janet" EmployeeID="3" LastName="Leverling">
      <Emp FirstName="Margaret" EmployeeID="4" LastName="Peacock">
        <Emp FirstName="Steven" EmployeeID="5" LastName="Devolio">
          <Emp FirstName="Nancy" EmployeeID="6" LastName="Buchanan">
            <Emp FirstName="Michael" EmployeeID="7" LastName="Suyama" /> 
          </Emp>
        </Emp>
      </Emp>
    </Emp>
  </Emp>
</root>

注
結果の階層の深さを変更するには、スキーマの sql:max-depth 注釈の値を変更し、それぞれの変更の後にもう一度テンプレートを実行します。

上のスキーマでは、すべての <Emp> 要素に、まったく同じ属性のセット (EmployeeID、FirstName、LastName) が指定されていました。次のスキーマはこれを少し変更したもので、マネージャに報告を行うすべての <Emp> 要素に対し、追加の ReportsTo 属性が返されます。

たとえば、次の XML フラグメントでは、従業員 1 の部下が示されています。

<?xml version="1.0" encoding="utf-8" ?> 
<root>
<Emp FirstName="Nancy" EmployeeID="1" LastName="Devolio">
  <Emp FirstName="Andrew" EmployeeID="2" 
       ReportsTo="1" LastName="Fuller" /> 
  <Emp FirstName="Janet" EmployeeID="3" 
       ReportsTo="1" LastName="Leverling">
...
...

変更後のスキーマは次のようになります。

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:documentation>
      Customer-Order-Order Details Schema
      Copyright 2000 Microsoft. All rights reserved.
    </xsd:documentation>
    <xsd:appinfo>
      <sql:relationship name="SupervisorSupervisee" 
                  parent="Emp"
                  parent-key="EmployeeID"
                  child="Emp"
                  child-key="ReportsTo" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="Emp" 
                   type="EmpType" 
                   sql:relation="Emp" 
                   sql:key-fields="EmployeeID" 
                   sql:limit-field="ReportsTo" />
  <xsd:complexType name="EmpType">
    <xsd:sequence>
       <xsd:element name="Emp" 
                    type="EmpType" 
                    sql:relation="Emp" 
                    sql:key-fields="EmployeeID"
                    sql:relationship="SupervisorSupervisee"
                    sql:max-depth="6"/>
    </xsd:sequence> 
    <xsd:attribute name="EmployeeID" type="xsd:int" />
    <xsd:attribute name="FirstName" type="xsd:string"/>
    <xsd:attribute name="LastName" type="xsd:string"/>
    <xsd:attribute name="ReportsTo" type="xsd:int" />
  </xsd:complexType>
</xsd:schema>

sql:max-depth 注釈

再帰リレーションシップで構成されるスキーマでは、スキーマ内に再帰の深さを明示的に指定する必要があります。これは、対応する FOR XML EXPLICIT クエリを正常に作成し、要求された結果を返すために必要です。

スキーマで記述されている再帰リレーションシップの再帰の深さを指定するには、スキーマで sql:max-depth 注釈を使用します。sql:max-depth 注釈の値は、再帰の回数を示す正の整数値 (1 ～ 50) です。値 1 を指定した場合は、sql:max-depth 注釈が指定されている要素で再帰が停止します。値 2 を指定した場合は、sql:max-depth が指定されている要素の次のレベルで再帰が停止します。このように、指定した値に応じたレベルで再帰が停止します。

注
この基本となる実装では、マッピングスキーマに対して指定されている XPath クエリが、SELECT ... FOR XML EXPLICIT クエリに変換されます。このクエリでは、再帰に有限の深さを指定する必要があります。sql:max-depth に指定する値が大きいほど、生成される FOR XML EXPLICIT クエリは大きくなります。これによって、取得に時間がかかることがあります。

この基本となる実装では、マッピングスキーマに対して指定されている XPath クエリが、SELECT ... FOR XML EXPLICIT クエリに変換されます。このクエリでは、再帰に有限の深さを指定する必要があります。sql:max-depth に指定する値が大きいほど、生成される FOR XML EXPLICIT クエリは大きくなります。これによって、取得に時間がかかることがあります。

注
アップデートグラムと XML 一括読み込みでは、max-depth 注釈は無視されます。つまり、再帰更新や再帰挿入は、max-depth に指定されている値に関係なく行われます。

複合要素での sql:max-depth の指定

sql:max-depth 注釈は、複合コンテンツを含む要素にも指定できます。

再帰要素

sql:max-depth を再帰リレーションシップの親要素と子要素の両方で指定した場合は、親で指定した sql:max-depth 注釈が優先されます。たとえば次のスキーマでは、親と子の両方の従業員要素に sql:max-depth 注釈が指定されています。この場合、<Emp> 親要素 (監督者) に指定されている sql:max-depth=3 が優先され、子の <Emp> 要素 (被監督者) に指定されている sql:max-depth は無視されます。

例 B

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="SupervisorSupervisee"
                                  parent="Emp"
                                  parent-key="EmployeeID"
                                  child="Emp"
                                  child-key="ReportsTo" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="Emp" type="EmployeeType" 
                          sql:relation="Emp" 
                          sql:key-fields="EmployeeID" 
                          sql:limit-field="ReportsTo" 
                          sql:max-depth="3" />
  <xsd:complexType name="EmployeeType">
    <xsd:sequence>
      <xsd:element name="Emp" type="EmployeeType" 
                              sql:relation="Emp" 
                              sql:key-fields="EmployeeID"
                              sql:relationship="SupervisorSupervisee"
                              sql:max-depth="2" />
    </xsd:sequence> 
    <xsd:attribute name="EmployeeID" type="xsd:ID" />
    <xsd:attribute name="FirstName" type="xsd:string"/>
    <xsd:attribute name="LastName" type="xsd:string"/>
  </xsd:complexType>
</xsd:schema>

このスキーマをテストするには、このトピックの例 A の手順に従ってください。

非再帰要素

再帰のないスキーマ内の要素に sql:max-depth 注釈が指定されている場合、その注釈は無視されます。次のスキーマでは、<Emp> 要素が <Constant> 子要素で構成され、この子要素が <Emp> 子要素で構成されています。

このスキーマでは、<Emp> 親要素と <Constant> 子要素の間に再帰がないため、<Constant> 要素に指定されている sql:max-depth 注釈は無視されます。しかし、先祖の <Emp> と子の <Emp> の間には再帰があり、このスキーマでは先祖と子の両方に sql:max-depth 注釈が指定されています。したがって、先祖 (監督者の <Emp>) に指定されている sql:max-depth 注釈が優先されます。

例 C

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="SupervisorSupervisee" 
                  parent="Emp" 
                  child="Emp" 
                  parent-key="EmployeeID" 
                  child-key="ReportsTo"/>
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="Emp" 
               sql:relation="Emp" 
               type="EmpType"
               sql:limit-field="ReportsTo"
               sql:max-depth="1" />
    <xsd:complexType name="EmpType" >
      <xsd:sequence>
       <xsd:element name="Constant" 
                    sql:is-constant="1" 
                    sql:max-depth="20" >
         <xsd:complexType >
           <xsd:sequence>
            <xsd:element name="Emp" 
                         sql:relation="Emp" type="EmpType"
                         sql:relationship="SupervisorSupervisee" 
                         sql:max-depth="3" />
         </xsd:sequence>
         </xsd:complexType>
         </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="EmployeeID" type="xsd:int" />
    </xsd:complexType>
</xsd:schema>

このスキーマをテストするには、このトピックの例 A の手順に従ってください。

制限により派生する複合型

<restriction> により派生する複合型がある場合、その基になる複合型の要素に sql:max-depth 注釈は指定できません。このような場合は、派生した型の要素に sql:max-depth 注釈を追加できます。

一方、<extension> により派生する複合型がある場合、その基本複合型の要素には sql:max-depth 注釈を指定できます。

たとえば、次の XSD スキーマでは、基本型に sql:max-depth 注釈が指定されているのでエラーが発生します。この注釈は、別の型の <restriction> から派生する型ではサポートされていません。この問題を解決するには、スキーマを変更し、派生した型の要素に sql:max-depth 注釈を指定する必要があります。

例 D

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes"
            xmlns:msdata="urn:schemas-microsoft-com:mapping-schema">
  <xsd:complexType name="CustomerBaseType"> 
    <xsd:sequence>
       <xsd:element name="CID" msdata:field="CustomerID" />
       <xsd:element name="CompanyName"/>
       <xsd:element name="Customers" msdata:max-depth="3">
         <xsd:annotation>
           <xsd:appinfo>
             <msdata:relationship
                     parent="Customers"
                     parent-key="CustomerID"
                     child-key="CustomerID"
                     child="Customers" />
           </xsd:appinfo>
         </xsd:annotation>
       </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <xsd:element name="Customers" type="CustomerType"/>
  <xsd:complexType name="CustomerType">
    <xsd:complexContent>
       <xsd:restriction base="CustomerBaseType">
          <xsd:sequence>
            <xsd:element name="CID" 
                         type="xsd:string"/>
            <xsd:element name="CompanyName" 
                         type="xsd:string"
                         msdata:field="CName" />
            <xsd:element name="Customers" 
                         type="CustomerType" />
          </xsd:sequence>
       </xsd:restriction>
    </xsd:complexContent>
  </xsd:complexType>
</xsd:schema>

このスキーマでは、CustomerBaseType 複合型に sql:max-depth が指定されています。また、CustomerBaseType から派生する CustomerType 型の <Customer> 要素も指定されています。このようなスキーマで指定される XPath クエリでは、制限の基本型で定義されている要素に sql:max-depth がサポートされていないので、エラーが発生します。

深い階層のスキーマ

要素に子要素が含まれ、その子要素にさらに別の子要素が含まれるというような深い階層のスキーマの場合は、指定されている sql:max-depth 注釈によって生成される XML ドキュメントで、階層が 500 レベルを超えた場合はエラーが返されます。ここでは、最上位要素をレベル 1、その子をレベル 2 と数えます。

次の方法で共有