連載　SQL ServerでXMLを操作する
最終回：リレーショナルDBにXML文書を追加する

ストアドプロシージャを利用したデータの追加

■OPENXMLの仕組み

　OPENXMLは、「第1回：リレーショナルDBからXML文書を取り出す」で説明したFOR XML句と共に、SQL Server 2000で新しく追加されたXML拡張機能の1つです。OPENXMLでは、XML文書で定義されたデータをレコードセットのように扱うことができるようになっています。OPENXMLは、ストアドプロシージャを通してXML文書をデータベースのテーブルにマッピングすることでデータを操作します。

　図1を使って説明します。まずXML文書をストアドプロシージャで解析します。解析されたXML文書は、メモリ上にレコードセットイメージで展開されます。最後にメモリ上のデータをINSERTやUPDATE文で処理します。

図1　OPENXMLでのデータの流れ

　OPENXMLの構文は次のようになっています。

OPENXML(@idoc, row, [flag]) WITH Schema

パラメタ	説明
@idoc	ドキュメントハンドル。読み込まれたXML文書が展開されている
row	テーブルのレコードにマッピングされるノードを識別するためのXPath式
flag	XML文書が属性を中心に記述されていれば1、要素を中心に記述されていれば2をセット
Schema	カラムとXMLノードのマッピングを記述。テーブル名やカラムの一覧を記述

　XML文書をストアドプロシージャで解析する場合、ストアドプロシージャからsp_xml_preparedocument（これもストアドプロシージャです）を呼び出します。OPENXMLを含むSQL文の実行後にはsp_xml_removedocumentを呼び出して、メモリ上に展開されたレコードセットイメージを削除する必要があります。

　sp_xml_preparedocumentの構文は次のようになっています。

sp_xml_preparedocument @idoc OUTPUT, @doc パラメタ 説明 @idoc ドキュメントハンドル。解析後のXML文書が展開されている @doc XML文書 ※構文にある、@idocの後のOUTPUTは、@idocがどのように利用されるかを表わすフラグです。ここでは出力先として利用しています。

　sp_xml_removedocumentの構文は次のようになっています。

sp_xml_removedocument @idoc パラメタ 説明 @idoc 削除するドキュメントハンドル

■ストアドプロシージャの作成と呼び出し

　ではストアドプロシージャを作成しましょう。まず、SQL Server 2000のEnterprise Managerを起動してデータの登録先のデータベースを選択します。そして新規のストアドプロシージャを作成して、テキストに下記のサンプルコードを入力します。「OK」を押して、ストアドプロシージャの作成は終わりです。

CREATE PROC InsData @doc varchar(8000) AS DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @doc INSERT INTO Person SELECT * FROM OPENXML(@idoc, '/Person', 2) WITH Person EXEC sp_xml_removedocument @idoc

OPENXMLを利用するストアドプロシージャのサンプルコード

　簡単にサンプルコードの説明をします。1行目はプロシージャの名前とパラメタが記述されていて、InsDataという名前で@docというパラメタを持っています。3行目では解析後のXML文書が格納されるドキュメントハンドルを定義しています。4行目でXML文書を解析するのですが、入力パラメタである@docを解析して、@idocに展開しています。そして5〜7行目のSQL文でOPENXMLを実行しています。ここでは、@idocに展開されているXML文書からPersonノードをキーとしてデータを取り出して、Personテーブルに登録しています。最後にハンドルを削除しています。

　ストアドプロシージャを作成したので、次はこれを呼び出すスクリプトを記述します。スクリプトはSQL文を構築して登録する方法と同じような内容になります。違いはストアドプロシージャを呼び出すための処理や、ストアドプロシージャに渡すデータの構築内容です。

　いつものようにオブジェクトを作成します。前項のINSERT文を生成する方法とは違い、SQL文を使用しないので文字列の定義はありません。作成するオブジェクトは、コネクションオブジェクトとコマンドオブジェクトです。コマンドオブジェクトは作成後に下記の情報を設定します。

objCmd.ActiveConnection = objCon; objCmd.CommandType = 4; objCmd.CommandText = "InsData";

ストアドプロシージャを呼び出す

　コネクションオブジェクト、コマンドタイプ、コマンドテキストを設定します。コマンドタイプの値はストアドプロシージャを表わすフラグです。コマンドテキストには実行するストアドプロシージャの名前を設定します。

■挿入されたデータを確認する

　ここでもXML文書の読み込みについては説明を割愛します。読み込んだXML文書から、先ほどと同様に各ノードに定義されている情報を取り出し、ストアドプロシージャで解析できるXML文書に再構築していきます。具体的には、先に作成したストアドプロシージャでPersonをキーとしているので、Personを親ノード、登録する情報を子ノードとしてXML文書を構築しますが、以下のようなスクリプトの記述となります。

strDat = "<Person>"; strDat += "<Name>" + tmpNd.selectSingleNode("氏名").text      + "</Name>"; strDat += "<Age>" + tmpNd.selectSingleNode("年齢").text      + "</Age>"; strDat += "<PostalCode>"      + tmpNd.selectSingleNode("郵便番号").text + "</PostalCode>"; strDat += "<State>" + tmpNd.selectSingleNode("都道府県").text      + "</State>"; strDat += "<City>" + tmpNd.selectSingleNode("市町村").text      + "</City>" strDat += "<Address>" + tmpNd.selectSingleNode("番地").text      + "</Address>"; strDat += "<Building>"      + tmpNd.selectSingleNode("建物").text + "</Building>"; strDat += "<Phone>"      + tmpNd.selectSingleNode("電話番号").text + "</Phone>"; strDat += "<MailAddr>"      + tmpNd.selectSingleNode("E-Mail").text + "</MailAddr>"; strDat += "</Person>";

XML文書を再構築していく

　このように構築したXML文書をコマンドオブジェクトのパラメタに設定して実行します。

objCmd.Parameters.Item(1).Value = strDat; objCmd.Execute;

　これでデータの挿入が行われるのです。あとはスクリプトとしての後処理を記述して終りです。

　実際にサンプルプログラムを用意しました。DataIns2.htmlは、ストアドプロシージャを呼び出してデータを登録するスクリプトのサンプル。PersonData2.xmlが、 登録するデータのサンプルです。

■INSERT文とOPENXMLのどちらを使うべきか

　今回のサンプルでは、元のXML文書で定義されているノード名と、データベースで定義されているカラム名が異なっていたので、XML文書を再構築してストアドプロシージャに渡しています。しかし両方が同じ名前で定義されているなら、単に元のXML文書を渡すだけでデータベースへの登録ができます。

　ここで紹介したINSERT文を組み立てる方法のいずれの方法でも、XML文書のノード名とデータベースのカラム名が異なっている場合は、INSERT文やXML文書を構築する必要があります。そのため、どちらの方法なら楽、ということはなかなかいえません。しかし一般に、データベース間でデータを移動する場合はOPENXMLを使用した方が簡単でしょう。もちろん、SQL Server同士でデータを移動するのであれば、わざわざXMLを使わなくてもいいでしょう。しかし、別のDBMSからデータを移動するのであれば、データをいったんXML文書化するのは有効な手段ではないかと考えます。

　これまで4回に渡ってSQL Server 2000でXML文書を扱う方法を説明してきましたが、いずれの説明も、入門的なものばかりだったと思います。実際のシステム開発でSQL Server 2000とXMLを利用するには、もっと複雑な利用形態やスクリプトの記述が必要となるでしょう。しかしその際にも、ここで説明したいくつかの方法を組み合わせることで実現できるはずです。これらの記事が読者の方々の参考になれば幸いです。

7/7

Index
SQL ServerでXMLを操作する
	SQL Server 2000でXML機能を使えるようにする ・SQL Server 2000のXML対応機能とは？ ・この記事で使用するデータ ・SQL Server 2000の設定
	リレーショナルデータをXML文書として取り出す ・リレーショナルデータとXML文書のマッピング ・SQL ServerのデータをXML文書として取り出す ・FOR XML句でデータを取り出す ・ELEMENTSオプションの指定 ・RAWモードの指定 ・EXPLICITモードの指定
	クライアントサイド・スクリプトでSQL Serverを操作する ・サーバサイド・スクリプトとクライアントサイド・スクリプト ・スクリプトでデータを取り出す ・取り出したデータのXML文書化
	XSLTでXML文書をHTMLに変換 ・XSLTスタイルシートの作成 ・クライアントサイド・スクリプトからXSLTを呼び出す ・スクリプトをサーバサイド・スクリプトに作り替える
	XPathを使って、SQL Serverでクエリを実行する ・データベースをXML文書に見せかける ・ダイレクトにXPathを指定する方法 ・テンプレートを使用する方法 ・スキーマを使用する方法
	XML文書形式のデータをSQL Serverに格納する方法 ・XML文書とテーブル構造の関係 ・SQL文の構築（INSERT文を構築する） ・サンプルプログラム
	ストアドプロシージャを利用したデータの追加 ・OPENXMLの仕組み ・ストアドプロシージャの作成と呼び出し ・挿入されたデータを確認する ・INSERT文とOPENXMLのどちらを使うべきか

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）