死、税金、そしてリレーショナルデータベース、第 1 部

Andrew Conrad
Microsoft Corporation

April 15, 2003
日本語版最終更新日 2003 年 10 月 23 日

概要 : リレーショナルデータとオブジェクト指向データ間のブリッジとして XML を使用し、 SQL Server をオブジェクトリポジトリに変換することにより、 SQL Server 2000 の XML 機能を説明します。

サンプルファイル (ObjectStorePart1.exe) のダウンロード。

必要条件 このダウンロードを使用するには、 Microsoft.NET Framework 1.0、Microsoft SQL Server 2000、および SQLXML 3.0 Service Pack 1 が必要です。

編集者のメモ今月の Extreme XML は、ゲストコラムニスト Andrew Conrad 氏が執筆しています。 Andy は、WebData XML チームのソフトウェアデザインエンジニアです。彼は、この 4 年間、SQL Server 2000 XML サポートおよび SQLXML Web リリースに取り組んできました。彼への連絡は、aconrad@microsoft.com に (英語で) お願いします。

はじめに

XML エンタープライズ開発の世界で確かなことは、死、税金、そしてリレーショナルバックエンドデータストアで作業する必要があることです。死や税金とまったく同様に、これは痛ましい努力になります。問題は、階層的に半構造化された XML データモデルと、正規化されたリレーショナルデータモデルの間の変換です。前者は、データの交換やビジネスデータの作成に適していますが、後者は固定ストレージに適しています。もし Ben Franklin が現在生きていたら、 XML データモデルの拡張性を応用した疎結合アーキテクチャを開発していただろう、エンタープライズ開発の 1 つの確かなものを嫌っていただろう、そして (人生で "確かなもの" である死と税金とは別に) 古くなったリレーショナルデータベースと統合する必要があるだろうということは憶測できます。しかし、現在は SQLXML というソリューションがあります。 SQLXML 3.0 は、SQL Server 2000 XML サポートを強化する SQL Server 2000 のアドオン製品です。開発者はこのソリューションを使って、2 つのデータモデル間での変換に多くの機能を提供することにより、 XML データベースとリレーショナルデータベースのギャップを埋めることができます。

注このコラムは SQLXML 3.0 で利用できる SQL Server XML 機能に基づいています。 SQL Server 2000 が 2000 年秋にリリースされて以来、 Microsoft の WebData グループは、3 つの完全サポート付きアップグレードを提供し、 XML を強化および改善してきました。このように頻繁にリリースが行われるのは、ユーザーの要望、および XML 標準が急速に変化しているためです。たとえば、Web Release 3 では、W3C XML Schema、SOAP、.NET Framework 用のサポートを追加します。最新の Web リリースをダウンロードするか、または XML for SQL Server Web Release で提供される新機能の詳細を探すには、 http://www.msdn.microsoft.com/sqlxml (英語) を参照してください。

一般的に、SQLXML 機能は、2 つの主要な領域に分けることができます。

サーバー側の機能。Transact-SQL 拡張機能を使用してアクセスできます。
クライアント側/中間層機能。ADO、HTTP、または .NET データプロバイダを使用してアクセスできます。

この資料では、サーバー側の機能に注目し、その機能を使用して SQL Server をオブジェクトリポジトリに変換する方法について説明します。この資料の第 2 部では、サーバー側の機能を同じような機能を提供するクライアント側の機能に置き換えます。どちらの資料でも各ソリューションの長所と注意点、および実際に使用する際の問題を解決するための機能の使用方法を説明します。

注この資料では、読者に SQLXML 機能に関する知識があることを前提としています。製品の概要については、「Microsoft SQL Server 2000 XML 機能概論」を参照してください。

XML シリアル化

Microsoft .NET Framework が提供する機能の 1 つに、共通言語ランタイムオブジェクトを XML に (逆もまた同様に) シリアル化する機能があります。「シリアル化」とは、オブジェクトの状態を転送、保存、再構築できる形式に変換する処理です。 .NET Framework は、オブジェクトの状態を XML としてシリアル化するために System.Xml.Serialization 名前空間のいくつかのクラスを提供します。 XML シリアル化の使用により、ビジネスオブジェクトのグラフ全体の状態をシリアル化できます。つまり、特定のアプリケーションの状態を論理的に表す XML ドキュメントを作成します。これはさまざまな用途で役に立ちます。たとえば、ユーザーが変更を取り消したり、エラーから回復できるように、チェックポイントでアプリケーションの状態を保存する場合や、ネットワーク経由でアプリケーションの状態を転送する場合にも役立ちます。この資料では、XML とリレーショナルデータ構造間で変換する場合に SQL Server の XML 拡張機能を使用するオブジェクトレポジトリのサンプルを作成します。

この資料は .NET Framework の XML シリアル化のいくつかの機能を詳しく説明していますが、 XML シリアル化の手引きを意図したものではありません。 XML シリアル化についてさらに詳しく知りたい場合は、「XML Serialization in the .NET Framework」 (英語) を参照してください。

サンプル

従業員情報が含まれている従業員管理アプリケーションの開発作業を想像してください。プロジェクトチームは、バージョン 1 ではスケジュールどおりに提供できるようにするため、ビジネス層には 2 つのクラスしか含めません。

public class Employee
{
private int _employeeID = -1;
   private string _lastName = null;
   private string _firstName = null;
   private string _title = null;
   public ArrayList _addressList = new ArrayList();        
 
   public Employee() {}
public Employee(int employeeID, string firstName, string lastName, string title)
   {
        _employeeID = employeeID;
      _firstName = firstName;
       _lastName = lastName;
       _title = title;
   }
    public string FName
     {
       get { return _firstName;}
       set { _firstName = value;}
    }  
    public string LName
{
       get { return _lastName;}
       set { _lastName = value;}
   }  
   public int ID
     {
        get { return _employeeID;}
        set { _employeeID = value;}
     }  
   public string Title
   {
      get { return _title;}
      set { _title = value;}
   }  
}
 
public class EmployeeAddress
{
    private string _streetAddress = null;
     private string _city = null;
     private string _state = null;
     private string _zip = null;
    private AddressType _type;

     public EmployeeAddress(){}
   public EmployeeAddress(AddressType type, string streetAddress, string 
   city, string state, string zip)
   {
      _type = type;
        _streetAddress = streetAddress;
       _city = city;
      _state = state;
      _zip = zip;
   }
   public string Street
   {         
get { return _streetAddress; }
      set {  _streetAddress = value; }
   }
   public string City
   {
       get { return _city; }
        set {  _city = value; }
     }     
    public string State
     {
        get { return _state; }
        set {  _state = value; }
   }
     public string Zip
   {         
get { return _zip; }
      set {  _zip = value; }
   }
   public AddressType Type
   {
        get { return _type; }
      set {  _type = value; }
   }
}

Employee クラスには、従業員 ID、氏名、および役職が含まれています。これはごくあたりまえのことです。その他に、各従業員には、一般的な住所情報をすべて含む (Employee の _addressList ArrayList に格納された) 0 個から多数の住所オブジェクトが含まれています。あまりに単純でも心配しないでください。ユーザーがバージョン 2 に多くのことを要求しているので、プロジェクトではまもなくビジネスロジックオブジェクトモデルを拡張することになります。

標準的なデータベースのデザインに従って、次のようなリレーショナルテーブルがビジネスオブジェクトの固定ストレージ用にデザインされています。

CREATE TABLE [dbo].[Employee] (
   [EmployeeID] [int] NOT NULL ,
   [LastName] [nvarchar] (50) NULL ,
   [FirstName] [nvarchar] (50) NULL ,
   [Title] [nvarchar] (50) NULL ,
   [Overflow] [ntext] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Address] (
   [EmployeeID] [int] NOT NULL ,
   [AddressType] [nvarchar] (10) NOT NULL ,
   [Street] [nvarchar] (200) NULL ,
   [City] [nvarchar] (100) NULL ,
   [State] [nvarchar] (2) NULL ,
   [Zip] [nvarchar] (20) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Address] ADD 
   CONSTRAINT [PK_Address] PRIMARY KEY  CLUSTERED 
   (
      [EmployeeID],
      [AddressType]
   )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employee] ADD 
   CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED 
   (
      [EmployeeID]
   )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Address] ADD 
   CONSTRAINT [FK_Address_Employee] FOREIGN KEY 
   (
      [EmployeeID]
   ) REFERENCES [dbo].[Employee] (
      [EmployeeID]
   )
GO

一般的に、このバージョンのアプリケーションでは、ビジネスロジッククラスとデータベーステーブルが非常によく似ています。実際に、ここで問題になるのは、プロパティと列の名前の一部が異なることと Address テーブルに外部キーとして EmployeeID が含まれていることだけです。オブジェクトをリレーショナルオブジェクトおよび全体にマップするときの一般的な問題は、特に問題はありません。実際、リレーショナルデータベースに変更を保存するために、多くの標準データベースアクセスメカニズムのいずれかを使用できます。ただし、ここでの目的は、アプリケーションが必要に応じてビジネスオブジェクト階層全体の保存に使用できる、あまり細かくないデータ保存層を導入することです。アプリケーションの最も一般的な使用例のパターンは以下のとおりなので、これをデザインする必要があります。

ユーザーはデータの特定のセットを選択します。
無期限にそのデータのセットを表示します。
一括処理でビジネスオブジェクトへの変更を保存します。

つまり、典型的な非接続クライアントの事例とあまり細かくない保存インターフェイスが最も適切に機能します。さらに、ビジネスオブジェクトをリレーショナルデータベースに保存するために必要な細かい制御を抽象化することにより、ユーザーのコードは、ドメイン固有のビジネスロジックについては検討する必要がありますが、データソースにも、データソースの更新に必要な処理にも煩わされることはありません。

(現在) 特に問題にならないもう 1 つの点として、リレーショナルメタデータには Employee テーブルの Overflow フィールドがあります。初期のデザインから、そのフィールドが含まれている理由は明確ではありません。実は、このフィールドは、拡張性というデザインの別の目的のために追加されています。この目的を達成する方法は別の問題なので、これについてはこの資料の後半で説明します。

オブジェクトリポジトリ

ビジネスロジックをデータベースアクセスコードから分離するために、オブジェクトリポジトリを作成します。このリポジトリは、ビジネスオブジェクトの保存と作成を担います。基本的には、リポジトリのユーザーには、CRUD (Create、Retrieve、Update、Delete) 操作用のインターフェイスが提供され、ビジネスオブジェクトのインメモリコレクションのように見えます。オブジェクトリポジトリの API は次のとおりです。

public class ObjectRepository
{    
   public ObjectRepository(String connectionString);
public Employee[] GetEmployee(string lastName);
public void PersistEmployee(Employee employee); 
public void PersistEmployees(Employee[] employee);
public void RemoveEmployee(Employee employee); 
}

このクラスには、従業員の名前に基づいた Employee オブジェクトを取得するメソッド、 Employee オブジェクトの配列への変更を保存するメソッド、および Employee オブジェクトを削除するメソッドがあります。 1 つの Employee オブジェクトを保存するために、ヘルパメソッドが用意されていることに注意してください。このメソッドは、背後で配列内の 1 つの Employee オブジェクトをラップし、従業員の配列を保存するためのメソッドと同じコードを使用します。

オブジェクトリポジトリ API により、コンシューマは元になるデータソースに注意する必要はありません。ユーザーはオブジェクトを検索し、ビジネスロジックを実行して、変更を保存するだけです。これにより、オブジェクトリポジトリはユーザーのコードに影響せずに、元になるストレージメカニズムを変更できます。さらに、オブジェクトリポジトリは、オブジェクトリポジトリに対して CRUD 操作を実行するビジネスロジック開発者向けに、保存層のオブジェクト指向の考え方を提供します。実際に、ObjectRespository クラスは、 IList インターフェイスまたは IDictionary インターフェイスを実装して、フレームワークのコレクションクラスのように動作できます。この抽象概念のもう 1 つの利点として、開発者は、データをキャッシュして、インターフェイスを変更せずにデータベースへのアクセスを削減できます。

それでは、新しい従業員の作成とオブジェクトリポジトリへの保存を見ていきましょう。最初に、コードは、単純に Employee オブジェクトを作成し、 PersistEmployee メソッドを使用してそのオブジェクトをオブジェクトリポジトリに渡します。

ObjectRepository  repository = new ObjectRepository(_connectionString);
Employee emp = new Employee(1, "Jeffrey", "Jones", "SYSTEMS ANALYST");
emp._addressList.Add((object)new EmployeeAddress(AddressType.Home, "111 
Oak", "Redmond", "WA", "98052"));
emp._addressList.Add((object)new EmployeeAddress(AddressType.Work, 
"1111 North Corporate Road", "Redmond", "WA", "98052"));
repository.PersistEmployee(emp);

次に、オブジェクトリポジトリコードは、 (1 人の従業員の保存と従業員の配列の保存に同じコードを共有できるように) Employee 配列の従業員をラップして、 Employee 配列を XML にシリアル化し、 XML をデータベース内の PersistEmployees ストアドプロシージャ (「重要な役割」セクションで説明します) に送信します。

private void PersistEmployee(Employee employee)
{
Employee[]employees = new Employee[1] {employee};
      MemoryStream stream = new MemoryStream();
      XmlRootAttribute xmlRoot = new XmlRootAttribute();
      xmlRoot.ElementName = "EmployeeList";
      Type type = employees.GetType();
      XmlSerializer serializer = new XmlSerializer(type, xmlRoot);
      serializer.Serialize(stream, (object) employees);
      stream.Position = 0;
      PersistChanges( new StreamReader(stream).ReadToEnd(), false); 
}
 
private void PersistChanges(string serializedEmployees, bool delete)
{
SqlXmlCommand command = new SqlXmlCommand(_SqlXmlconnectionString);
      command.CommandType = SqlXmlCommandType.Sql;
      command.CommandText = "exec PersistEmployees ?, ?";
      SqlXmlParameter param = command.CreateParameter();
      param.Value = serializedEmployees;            
      SqlXmlParameter param2 = command.CreateParameter();
      param2.Value = delete ? 1 : 0;  
      command.ExecuteNonQuery();
}

XMLSerializer を作成するときに、 XmlRootAttribute が含まれることに気が付きます。これは、明示的に指定できるルート要素の名前です。 XML シリアル化は XmlRootAttribute が指定されていない場合、 ArrayOfEmployee という名前を使用します。その結果、シリアル化の形式を変更しないと、 Employee オブジェクトを格納するコレクションの種類を変更できません。

また、シリアル化されたオブジェクト階層をデータベースに渡すときに、シリアル化されているオブジェクトをデータベースから削除する必要があるかどうか、そのリストを挿入または更新 (オブジェクトが既にデータベースに存在するかどうかによって異なります) として解釈する必要があるかどうか示すビット値を含めることに注意してください。このフラグを削除して、データベース内に複数のストアドプロシージャを含むことができます。ただし、これは、操作によって、異なるコマンドテキストを指定する必要があることを意味しているため、デザインがより適切になることはありません。

Employee クラスを簡単に見てみると、このデザインがデータベースとは異なるメンバ変数名や列名をどのように処理しているかが示されています。基本的に、XmlElement 属性を追加することにより、オブジェクトの状態用に生成する XML 内で使用する名前を制御できます。

public class Employee
{
   [XmlArrayItem(ElementName= "Address", Type=typeof(EmployeeAddress))]
     [XmlArray("AddressList")]
     public ArrayList _addressList = new ArrayList();

[XmlElement(ElementName = "FirstName")]
     public string FName
     {
      get { return _firstName;}
      set { _firstName = value;}
    }     
     [XmlElement(ElementName = "LastName")]
     public string LName
    {
        get { return _lastName;}
       set { _lastName = value;}
     }  
    [XmlElement(ElementName = "EmployeeID")]
     public int ID
    {
      get { return _employeeID;}
       set { _employeeID = value;}
     }     
     public string Title
     {
      get { return _title;}
      set { _title = value;}
     }     
   ...
}

XmlElement 属性で指定した名前は、データベース内の名前と必ずしも一致する必要はありません。データベース層は、名前の変化に対応できます。ただし、プロパティ名と異なる名前を付けると、オブジェクトリポジトリやデータベース層を変更する必要なく、プロパティ名を自由に変更できるようになります。

もう 1 つの興味深い点として、 _addressList メンバ用に XmlArrayItem 属性が明示的に含まれています。明示的に含めないと、XmlSerializer オブジェクトからエラーが発生します。このエラーは、Employees のシリアル化を試行したときに EmployeeAddress 型が予期されていなかったことを示します。この問題を解決する方法はいくつかありますが、XmlArrayItem 属性を追加して、 XML シリアル化に EmployeeAddress 型を認識させることが最も簡単な方法です。

オブジェクトを取得するコードは、保存するコードとよく似ていますが、根本的に逆の処理を行っています。この場合、データ層は、変更の保存に使用したのと同じ XML 形式で従業員オブジェクトの配列を送信します。

public Employee[] GetEmployee(string lastName) 
{
SqlXmlCommand command = new SqlXmlCommand(_SqlXmlconnectionString);
      command.CommandType = SqlXmlCommandType.Sql;
      command.CommandText = "exec FetchEmployee ?";
        SqlXmlParameter param = command.CreateParameter();
      param.Value = lastName;
      return DeserializeEmployee(command.ExecuteXmlReader());
}
 
private Employee[] DeserializeEmployee(XmlReader reader)
{
      XmlRootAttribute xmlRoot = new XmlRootAttribute();
      xmlRoot.ElementName = "EmployeeList";
      XmlSerializer serializer = new XmlSerializer(typeof(Employee[]),xmlRoot);
      Employee[] emps = (Employee[]) serializer.Deserialize(reader);
      reader.Close();
      return emps;
}

すばらしいことに、XML をビジネスオブジェクトとして逆シリアル化するための指示コードを記述する必要はありません。同一のコードが両方向に機能します。一般的に、オブジェクトリポジトリはかなり単純なデザインです。オブジェクトリポジトリは、単にオブジェクトと XML 間でシリアル化を行い、データベースに渡すだけです。そのため、この事例の転送メカニズムとして XML を選択する際の 2 つの利点のうち 1 つ目がここにあります。つまり、XML シリアル化がプログラム作成に手間をかけることなく、クライアント側で作成する大多数のコードを提供できることです。

OpenXML の概要

オブジェクトリポジトリは、シリアル化されたオブジェクトをリレーショナルデータ構造に変換するために、 SQL Server 2000 XML サポートの OpenXML 機能を使用します。 OpenXML には、 SQL Server プロセス内部のインメモリ行セットとして XML ドキュメントを表す機能があります。 OPENXML は行セットプロバイダなので、行セットプロバイダ (テーブル、ビュー、OPENROWSET 関数) を使用できる Transact- SQL ステートメントで使用できます。オブジェクトリポジトリの例に戻る前に、OpenXML をもう少し詳しく見てみましょう。機能を説明するのに最適な方法として、いくつか簡単な例を使用します。

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- ドキュメントから order detail を選択します。
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')
      WITH (ProdID      int         '@ProductID',
                Qty         int         '@Quantity')

EXEC sp_xml_removedocument  @idoc

XML から Order Detail 行を選択します。

ProdID      Qty         
----------- ----------- 
11            12
42            10

(2 件処理されました)

この例では、 XML ドキュメントが Root、Customer、Order、および OrderDetail という要素で構成されています。 OPENXML ステートメントは、2 列の行セット (ProdID と Qty) に OrderDetail 要素を取得します。最初に、sp_xml_preparedocument ストアドプロシージャを呼び出し、ドキュメントハンドルを取得します。このドキュメントハンドルを OPENXML ステートメントに渡します。 OPENXML ステートメントでは、 rowpattern (/ROOT/Customer/Order/OrderDetail) が処理する OrderDetail 要素を識別します。 WITH 句には、XML ノードと行セット列の間のマッピングが含まれています。

注この資料は、OpenXML の完全なチュートリアルを意図したものではありませんが、この資料のサンプル従業員アプリケーションのオブジェクトリポジトリの開発に役立ついくつかの機能に注目しています。OpenXML の詳細については、「OPENXML による XML の書き込み」を参照してください。

OpenXML の重要な機能の 1 つに、 XML をリレーショナルテーブルに "シュレッド" する機能があります。 XML とリレーショナルメタデータ間でマッピングを行うと、 XML で表現したツリーをリレーショナルテーブルに簡単に変換できます。このリレーショナルテーブルは、標準的なリレーショナルメカニズムで操作および検索できます。オブジェクトリポジトリの場合、この機能を使用して、シリアル化されたビジネスオブジェクトのビューを、標準的なリレーショナル形式にシュレッドすることができます。 OPENXML ステートメントの WITH 句に ColPattern パラメータを指定すると、この処理が実行されます。リレーショナル列の ColName (名前) と ColType (データベースの型)、および XML ドキュメントにマップする要素または属性を示す XPath パターンを提供することによって、 ColPattern を指定します。上記の例から、行セットの ProdID 列と Qty 列は、 Order Detail 要素の ProductID 属性と Quantity 属性にマップされます。

XPath パターンを指定すると、XML ツリーのノードを識別できるので、 XML ツリーをリレーショナル行セットに簡単にフラット化できることに注意してください。 XPath 言語は、階層的な半構造化されたデータの参照用に特別にデザインされているので、この操作が可能になります。この機能の柔軟性を説明するために、上記の例を拡張して、Customer 行と Order 行を選択しましょう。

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" EmployeeID="5" 
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- customer 行を選択します。
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer')
      WITH (CustomerID  varchar(10) '@CustomerID',
            Quantity       int           
            'Order/OrderDetail[@ProductID=''11'']/@Quantity',
            Contact    varchar(50) '@ContactName')

-- order detail を選択します。
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')
      WITH (OrderID     int         '../@OrderID',
            OrderDate   datetime    '../@OrderDate',
            ProdID      int         '@ProductID',
            Qty         int         '@Quantity')

EXEC sp_xml_removedocument  @idoc

クエリの結果は次のとおりです。

CustomerID Quantity      Contact                                            
---------- -----------  -------------------------------------------------- 
VINET      12           Paul Henriot

(1 件処理されました)

OrderID     OrderDate                    ProdID      Qty         
----------- -----------------------    ----------- ----------- 
10248       1996-07-04 00:00:00.000     11          12
10248       1996-07-04 00:00:00.000     42          10

(2 件処理されました)

ここで注意すべきことが 2 点あります。まず最初に、いくつかのフィールドが Order Details 行に追加されたことです。特に、行セットの OrderID、CustomerID、および OrderDate 列が、親 Order 要素の属性にマップされます。ほとんどの場合、OrderID フィールドは Order Details 行の外部キーです。そのため、親要素に移動する機能を使用して、階層的な XML の関係を主キー - 外部キーの関係に変換します。次に、Customer 行では、 OpenXML ステートメントが Order Details 要素をフィルタ選択するために、 Order Details 要素を下に移動して、 ProductID 属性に対する述語を実行し、 1 つの Order Detail に Quantity 属性を返しました。おそらく提供されたサンプルデータでは理解できませんが、ここでは機能を簡単に確認します。

OpenXML のもう 1 つの重要な機能として、オープンコンテンツ、具体的には OpenXML ステートメントで明示的にマップされていない XML の要素と属性を使用する機能があります。この操作の実行例は次のとおりです。

DECLARE @idoc int
DECLARE @doc varchar(1000)
-- サンプル XML ドキュメント
SET @doc ='
<root>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- オープン コンテンツを使用して order を選択します。
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 8)
      WITH (oid char(5), 
            date datetime,
            comment ntext '@mp:xmltext')
EXEC sp_xml_removedocument @idoc

クエリの結果は以下のとおりです。

oid     date                  comment                                     
-----  ----------------------   --------------------------------------------
O3    1999-07-14 00:00:00.000 <Order amount="100" note="Wrap it blue 
white red">
<Urgency>Important</Urgency></Order>
O4    1996-01-20 00:00:00.000 <Order amount="10000"/>

ここでは、コードは Order 行を選択しており、コメントフィールドを特別な @mp:xmltext 属性にマップして、 Order 要素の XML コンテンツをコメントフィールドに設定することを指定しました。さらに、コードは OpenXML コマンドの flags パラメータに値 '8' を渡すことにより、 Order 要素の未使用の XML コンテンツのみをコメントフィールドに送信することを指定しました。この @mp:xmltext は「メタプロパティ」と呼ばれ、@mp:xmltext による XML ドキュメントの処理を可能にする OpenXML コマンドにとって特別な意味を持っています。つまり、データベース保存コードの開発中に認識されないコンテンツを使用して、データベースコードを更新する必要なく、XML を変更および拡張できます。これは、ビジネスオブジェクトの拡張を処理するためにこの資料のサンプルアプリケーションで使用する機能です。

重要な役割

それでは、データベースの変更を保存するために、オブジェクトリポジトリで使用するストアドプロシージャを見てみましょう。

CREATE PROCEDURE PersistEmployees 
   @employeeXML nvarchar(4000),
   @remove bit = 0 
AS

DECLARE @hdoc int

exec sp_xmlxml_preparedocument @hdoc OUTPUT, @employeeXML

DELETE Address where EmployeeID in
(SELECT EmployeeID
FROM OpenXML(@hdoc,'/EmployeeList/Employee')
     WITH (EmployeeID     int 'EmployeeID'))

DELETE Employee where EmployeeID in
(SELECT EmployeeID
FROM OpenXML(@hdoc,'/EmployeeList/Employee')
     WITH (EmployeeID     int 'EmployeeID'))

if (@remove = 0)
BEGIN
    INSERT INTO Employee(EmployeeID, LastName, FirstName, Title, Overflow)
    SELECT EmployeeID, LastName, FirstName, Title, Overflow 
    FROM OpenXML(@hdoc,'/EmployeeList/Employee', 8)
    WITH (EmployeeID         int                'EmployeeID',
            LastName        nvarchar(100)       'LastName',
              FirstName       nvarchar(100)       'FirstName',
              Title           nvarchar(100)       'Title',
              Address         nvarchar(1)       'AddressList',
            Overflow        ntext               '@mp:xmltext')

    INSERT INTO Address(EmployeeID, AddressType, Street, City, State, Zip)
    SELECT EmployeeID, AddressType, Street, City, State, Zip
    FROM OpenXML(@hdoc, '/EmployeeList/Employee/AddressList/Address', 8)
    WITH (EmployeeID   int       '../../EmployeeID',
              AddressType     nvarchar(10)      'Type',
              Street          nvarchar(200)      'Street',
              City            nvarchar(100)      'City',
              State           nvarchar(4)         'State', 
              Zip             nvarchar(20)      'Zip')
END

まず注意すべき点は、1 つのストアドプロシージャで挿入、更新、および削除の操作を実行していることです。ストアドプロシージャには、 XML としてシリアル化された従業員の一覧 (@employeeXML)、および従業員を削除するかどうか指定するビット (@remove) という 2 つのパラメータがあります。ストアドプロシージャは、@remove が True かどうかに関わらず、最初に、EmployeeID に基づいて Employee テーブルおよび Address テーブルから行を削除します。このような削除ステートメントを基礎にして、1 人以上の従業員を削除できることに注意してください。 @remove が False の場合は、Employee テーブルおよび Address テーブルに行が挿入されます。これは更新 (削除してから挿入) を処理するにはかなり稚拙な方法ですが、現時点では十分です。削除ステートメントと同様に、複数の Employee 行や Address 行を追加できました。その結果、ストアドプロシージャ全体は複数の Employees への変更を一度に処理できます。

実際に、Employee テーブルと Address テーブル間の主キーおよび外部キーのリレーションシップの処理は、かなり単純なことに気が付きます。必要なのは、最初に Employee を挿入し、その後、Address テーブルの挿入ステートメントで Address 要素の親に移動して、主キーの EmployeeID 値を取得することだけです。そのため、Address 要素に外部キー値は含まれていませんが、階層は OpenXML ステートメントのリレーションシップを保存し、そのリレーションシップをリレーショナルテーブルに保存できるようにします。

ストアドプロシージャのもう 1 つの興味深い点として、 @mp:xmltext プロパティを指定すると、 Employee 要素の未使用のコンテンツすべてが Employee テーブルのオーバーフロー列に設定されることがあります。現時点では、Employee 要素のすべてのコンテンツを OpenXML ステートメントが使用しているため、実際にこの操作は行われません。つまり、Employee の子要素および子属性すべてが WITH 句にマップされます (AddressList 要素のマッピングは、挿入ステートメントで使用されていない場合にも含まれます。このように、住所の実体が Address テーブルに挿入されないので、住所はオーバーフローに加えられません)。一般的に、このオーバーフローマッピングを指定すると、Employee ビジネスオブジェクトの拡張時に、オブジェクトリポジトリは同じストアドプロシージャを再利用できます。

この時点で、オブジェクトリポジトリはシリアル化されたビジネスオブジェクトをリレーションテーブルにシュレッドすることができるようになります。ところで、ここでの実際の利点は何でしょう? オブジェクト階層全体を XML としてデータベースのテキスト列に格納することはできないのでしょうか? XML を解析するコードを記述できる場合を除いて、その答えは「いいえ」です。理由は、データをリレーショナル構造にシュレッドすることにより、従来の Transact-SQL コードをより簡単にクエリできるからです。

ここで、シュレッドされたデータから XML シリアル化の形式を生成するクエリを検証しましょう。

CREATE PROCEDURE FetchEmployee
   @LastName nvarchar(100)
AS

SELECT 1   as Tag, 
       null       as Parent,
       null       as [EmployeeList!1!],
       null       as [Employee!2!EmployeeID!element],
       null       as [Employee!2!LastName!element],
       null       as [Employee!2!FirstName!element],
       null       as [Employee!2!Title!element],
       null       as [Employee!2!!xmltextxmltext],
       null       as [AddressList!3!],
       null       as [Address!4!Street!element],
       null       as [Address!4!City!element],
       null       as [Address!4!State!element],
       null       as [Address!4!Zip!element],
       null       as [Address!4!Type!element]
UNION ALL
SELECT 2, 1, null, EmployeeID, LastName, FirstName, Title, Overflow ,null, 
null, null, null, null, null
FROM Employee
where LastName = @LastName
UNION ALL
SELECT 3, 2, null, EmployeeID, null, null, null, null, null, null, null, 
null, null, null
FROM Employee
where LastName = @LastName
UNION ALL
SELECT 4, 3, null, Address.EmployeeID, null, null, null, null, null, 
Street, City, State, Zip, AddressType        
FROM Address, Employee
where Address.EmployeeID = Employee.EmployeeID and Employee.LastName = @LastName
order by [Employee!2!EmployeeId!element], Tag, Parent
FOR XML EXPLICIT

このストアドプロシージャは、 Employee テーブルで LastName 列をフィルタ選択することにより、特定の名前に基づいて Employee 行および Address 行を選択する FOR XML Explicit クエリです。保存コードが Employee 要素および Address 要素をシュレッドしなかったら、この作業は急激に難しくなるでしょう。なぜなら、取得コードはシリアル化された XML を検索して、フィルタ選択用に LastName、および結合用に EmployeeID を検出する必要があるからです。

SQL Server 2000 の FOR XML Explicit モードのサポートを使用したことがない場合は、「EXPLICIT モードの使用」を参照してください。一般的に、EXPLICIT モードは FOR XML サポートの最も優れたバージョンです。これを使用すると、ユーザーは事実上、クエリの結果を適当な XML 形式にできます。この場合、Employee を取得するストアドプロシージャは、オーバーフローの (未使用の) コンテンツをフェッチする必要があります。 EXPLICIT モードは、オーバーフローをクエリの結果にマージすることをサポートする唯一のモードなので必要とされました。実際に、このストアドプロシージャは、OpenXML シュレッド処理を無効にして、 Employee のビジネスオブジェクト間の移動を可能にします。

XML をシュレッドすることにより、XML リレーショナルクライアント以外でもより簡単にデータにアクセスできます。つまり、ADO.NET および SQLClient で Employee テーブルを使用する Employee レポーティングツールを作成できます。実際には、このデザインでは、共通の保存データモデルとしてリレーショナルストレージが選択されています。他のアプリケーションおよびクライアントは、データにアクセスするためにリレーションまたは XML API のいずれかを自由に選択できます。

ビジネスオブジェクトを拡張する

Version 1.0 のリリース直後、アプリケーションの次期バージョンの作業が始まり、ユーザーは Employee オブジェクトの新しい条件の一覧を待っています。特に、各従業員は電話番号情報を加えて、作業中のプロジェクトにマップする必要があります。これらの追加を処理するために、新しい 2 つのクラスの導入が決まりました。 1 つは、元の Employee クラスのサブクラスである Employee2、もう 1 つは、Project クラスです。

    public class Employee2 : Employee
    {   
        private string _workPhone = null;
        private string _homePhone = null;
        private string _cellPhone = null;
        private Project _project = null;       
 
        public Employee2(int employeeID, string firstName, string 
        lastName, string title) 
            : base(employeeID, firstName, lastName, title) {}
 
        public Employee2() : base() {}
        
        public Project Project
        {
            get {return _project;}
            set {_project = value;}
        }
 
        public string WorkPhone
        {
            get {return _workPhone;}
            set {_workPhone = value;}
        }
        
        public string HomePhone
        {
            get {return _homePhone;}
            set {_homePhone = value;}
        }
        
        public string CellPhone
        {
            get {return _cellPhone;}
            set {_cellPhone = value;}
        }         
    }
 
        public class Project
    {
        [XmlElement(ElementName = "ProjectName")]
        public string _projectName = null;
        [XmlElement(ElementName = "Manager")]
        public int _manager = -1;
        
        public Project(){}
        public Project(string projectName, int manager)
        {
            _projectName = projectName;
            _manager = manager;
        }          
    }

リレーショナルデータベースアクセスコードに対する従来のオブジェクトでは、このような変更はリレーショナルストレージの構造への変更を意味していました。おそらく、2、3 のフィールドを Employee テーブルに追加し、主キー - 外部キーの関係を含む Project テーブルを Employee テーブルに追加します (DBA を使用すると、この操作は歯を抜くように簡単にできることがわかるでしょう)。ただし、XML はリレーショナルデータベースへの転送手段として使用され、データベースデザイナはオーバーフローフィールドを最初の Employee テーブルに含めることによって今後の計画を立てるので、オブジェクトリポジトリは、リレーショナルデータベース構造、ストアドプロシージャ、またはオブジェクトリポジトリコードを変更することなく、新しいビジネスオブジェクトをサポートできます。たとえば、Employee2 オブジェクトを保存するコードは次のとおりです。

Employee2 emp = new Employee2(3, "Don", "Johnson", "Developer");
emp._addressList.Add((object)new EmployeeAddress(AddressType.Other, 
"7878 Fleet Street", "Redmond", "WA", "98052"));
emp._cellPhone = "555-555-5151";
emp._homePhone = "111-111-9090";
emp.Project = new Project("Code Refactoring",1);
 _repository.PersistEmployee(emp);

Employee2 オブジェクトをフェッチします。

Employee[] employees = _repository.GetEmployee("Johnson");
Employee2 emp = (Employee2) employees[0];

これは基本的に元のビジネスオブジェクトで使用したのと同じコードであることに注意してください。その唯一の相違点は、Employee2 オブジェクトが明示的に作成されたことです。 EmployeeFactory を追加することで、この相違点を取り出すことができることにも注意してください。その結果、オブジェクトリポジトリの使用者から型作成を見えないようになりました。一般的に、オブジェクトリポジトリは拡張可能なデザインであることがわかりました。実際、ビジネスオブジェクト階層から作成される XMLドキュメントの形式を大幅に変更する場合を除いて、ビジネスオブジェクトの開発者は自由に好きなだけオブジェクトを拡張またはカスタマイズできます。たとえば、このカスタマイズが Employee オブジェクトからフィールドを削除する (コードの大部分が機能しなくなる) のと同じくらい徹底的に行われることがあります。この場合、OpenXML コードでは、シリアル化形式の要素が検出され、代わりに NULL 値が挿入されます。

ここでは、いくつかの技術的詳細に注目します。まず第 1 に、Employee2 を Employee クラスのサブクラスとして追加したとき、 Employee クラスに XmlInclude 属性を追加する必要がありました。

[XmlInclude(typeof(Employee2))]
public class Employee
{
   …
}

この属性を加えて、新しい Employee2 型を指定することにより、 XmlSerializer はシリアル化および逆シリアル化時に基本型と派生型の両方を認識できます。シリアル化する場合、 XMLSerializer はシリアル化されるインスタンスの種類を動的に割り出すことができるので、この操作はそれほど複雑ではありません。ただし、逆シリアル化する場合、XMLSerializer は、実際、保存された Employee2 オブジェクトのデータベースから XML をフェッチするときに Employee2 型を作成します。 XmlSerializer はこれをどのように割り出すのでしょうか? この質問に対する鍵はデータベースのオーバーフローコンテンツの検証にあります。

データベースをシュレッドするコードを実行した後、 Employee2 オブジェクトのリレーショナルデータはどのように見えるでしょうか? Employee テーブルおよび Address テーブルの標準的なリレーショナルフィールドの場合、すべてのものが Employee オブジェクトの場合と同じです。ただし、次の XML は、Employee テーブルのオーバーフローフィールドに含まれています。

<Employee xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance 
xsi:type="Employee2">
<HomePhone>111-111-9090</HomePhone>
<CellPhone>555-555-5151</CellPhone>
   <Project>
      <ProjectName>CodeCleanUp</ProjectName>
      <Manager>1</Manager>
   </Project>
</Employee>

基本的に、OpenXML シュレッドコードは、 Employee 要素のマップされていないすべてのコンテンツを取得し、そのコンテンツをオーバーフローフィールドに格納しました。もう 1 つの考え方として、 OpenXML コードが XML 内の特定のノードの (WITH 句で) マッピングを検索するたびに、最初の XML ツリーから削除する必要があります。すべてのマッピングを完了したら、残りのツリーを XML にシリアル化し、オーバーフロー列に配置します。実際、興味深いことに、XmlSerialization コードによって xsi:type 属性が追加されています。これは、まさに、逆シリアル化コードによりフェッチ時にデータベースに保存された型を動的に判断する方法です。特に標準のリレーショナル列にシュレッドされた場合、この機能を使用して、リレーショナルデータベースレベルで型を使用または操作する方法によって異なります。たとえば、レガシ Employee データを新しい Employee2 型にアップグレードしたり、クエリ述語の一部としてオブジェクトの型に基づいたクエリを実行できます。

考えられる拡張機能

説明したとおり、オブジェクトリポジトリのデザインはビジネスロジック層とかなり柔軟に結合されます。これは本来のデザインの目的でした。しかしながら、その全体的な実用性を促進するためにオブジェクトリポジトリのデザイン対して作成される拡張機能があります。

現在、更新および削除操作では、ビジネスオブジェクトのシリアル化されたイメージ全体がデータベースに送信されます。更新時に更新した値、および更新と削除の同時実行に使用した値を含めるだけで、明らかにそのイメージを削減できます。ただし、新しいビジネスオブジェクトと更新されたビジネスオブジェクト間の相違を監視するためのオブジェクトリポジトリが必要になります。オブジェクトリポジトリは同時実行制御に使用される値を認識する必要があるので、オブジェクトリポジトリをデータベースレイアウトと結合します。そのため、基本的には、オブジェクトレポジトリは、そのデザインとパフォーマンス間で比較検討されることになります。
同時実行制御メカニズムが適しています。現在のデザインは、同時実行の問題を確認するために EmployeeID のみを使用しています。実際は、更新コードは最初に削除してから挿入するので、同時実行の問題のエラー確認は行いません。結局、このコードにより、オブジェクトリポジトリはバックエンドデータベースから削除されたオブジェクトを更新できます。ただし、データベース層は OpenXML を使用して XML をリレーショナル構造にシュレッドするので、従来のリレーショナルデータベースコードが同時実行の問題を処理するのと同じ方法でこの問題を処理できます。
オブジェクトリポジトリの例で使用されなかった OpenXML の機能の 1 つに、列パターンや行パターンの一部に "//" や XPath ワイルドカードを指定する機能があります。この機能を使用すると、データベースコードをビジネスロジック層から分離できます。これにより、データベースコードを変更することなく、(エンティティ名が変更されない限り) オブジェクトをシリアル化した XML の形式を変更できます。アプリケーションがこの種の柔軟性を必要とする場合のみ、列および行のパターンをあいまいにする必要があることに注意してください。
XML としてオーバーフローを格納することは、XML API 以外のアクセスにとってあまり適切なことではありません。この問題を解決するために考えられる 1 つの選択肢として、 OpenXML を使用して、データをエッジテーブル形式にシュレッドします。エッジテーブル形式は、1 つのテーブル内でのきめの細かい XML ドキュメントの構造 (たとえば、要素または属性の名前、ドキュメント階層、名前空間など) を表します。ただし、クエリの開発者がテーブルで表す階層を依然として理解する必要があるように、エッジテーブル用のクエリコードの記述も Transact SQL ステートメントの記述ほど単純ではありません。一般的には、現在、この問題を解決する明確な方法はありません。実際、オーバーフローの柔軟性とデータへのアクセスとの対比がアプリケーション開発者にとって重要なことになります。

まとめ

どのデザインでも同様に、間違いなく比較検討する部分があります。転送手段としての XML は、明らかにデータベースアクセス用の最も優れたパフォーマンスメカニズムではありません。多くのマークアップがネットワーク経由で転送されます。ただし、ここで説明したように、データベース間の転送手段として XML を使用し、保存メカニズムとして OpenXML を使用することにより、ビジネスロジック層からデータベース層を明確に分離できます。その結果、柔軟性があり拡張性のあるデザインを開発できます。一般的に、デザインは特定のアプリケーションの特別な必要条件になります。

SQLXML マッピングテクノロジによるクライアント側の操作により、 XML をシュレッドまたは生成するコードをデータベースサーバーから移動できます。この資料の第 2 部では、このテクノロジを検証し、オブジェクトリポジトリを再分析してこのテクノロジを使用します。

死、税金、そしてリレーショナル データベース、第 1 部