ストアドでXMLファイルを読込む方法は？

SQLServer2005のストアドプロシージャでXMLファイルを読込む方法を教えてください。

No.1 回答者： kero_mio 回答日時： 2007/08/14 16:56

SQL Server上でXMLファイルを読み込むには、

「SQL Server Integration Services(以下、SSIS）」の機能を
使えばできますが、ストアドではできません。
もし、.NET + VB.NET の技術をお持ちであれば、SSISの機能で
作ることをお勧めします。

SSISについて
http://www.microsoft.com/japan/sql/prodinfo/feat …

＃　ストアドから、SSISのモジュールを呼び出すことも可能です。
　　（sp_start_jobというストアドで呼び出せます）

ただし、SSISじゃなく、どうしてもストアドでやりたい場合は、
制限付ではありますが、動くサンプルを作ってみました。

制限内容：
・XMLファイルがSJISであること
・SQL Serverの設定で、xp_cmdshellストアドが動く
　サーバ環境にあること

# xp_cmdshellの動作を許可するには、事前に下記のストアドを
　流し、管理者ユーザで、サーバ設定を変更する必要があります。
----------------------------------------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
----------------------------------------------------------

もし、それらの制限が回避できるのであれば、下記のコードで
XMLファイルをストアドから読み込むことができます。

master DBにある「dbo.xp_cmdshell」というストアドを呼び出し、
XMLの内容をDOSのtypeコマンドで取得。
取得した結果を「#」付のテーブル名（ようは、tempDBに一時格納）
させ、展開させ、後は、sp_xml_preparedocument やOPENXMLを
使って、XMLを目的に合わせてデータ処理すれば、宜しいかと
思います。

■ストアドからXMLファイルを読み込む

declare @buffer nvarchar(max)
declare @maxLine int
declare @i int
declare @line nvarchar(max)

-- tempdbに一時テーブルを作成する
create table #tempXML(lineBuffer nvarchar(max))

-- DOSのTYPEコマンドでXMLファイルの内容を表示させ、tempdbに作成した#tempXMLテーブルに内容を書き込む
insert into #tempXML exec master.dbo.xp_cmdshell 'type C:\abc.xml'

-- 初期化
SET @i = 0
SET @buffer = ''

-- XMLファイルの行からバッファを生成する
declare cur cursor for select * from #tempXML
open cur
fetch next from cur into @line
while @@FETCH_STATUS = 0
begin
　if @line is not null and PATINDEX('%<!--%', @line) = 0
　　begin
　　　set @buffer = @buffer + @line
　　end
　　fetch next from cur into @line
end
close cur
deallocate cur

-- XMLファイルから読み込んだXML文字列を取得する
select @buffer

-- 一時テーブルを削除する
drop table #tempXML

-- XMLパーサーでXML用のノード階層を作る
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @buffer

-- ノードをテーブルに格納したイメージを表示
select *
from OPENXML (@hdoc, '/views/view',1)

-- XMLパーサー解析用ハンドルを閉じる
exec sp_xml_removedocument @hdoc

長くなりましたが、ご参考になれば、幸いです。