XMLファイルを登録する

SQL Server

いままでXMLファイルをDBで管理・検索するのに、テーブルに検索対象としたい要素ごとのカラムを用意してインサートしていました。わりとフラットな構造のXMLだったのでそれほど困っていないのですが、SQL ServerにはXML型データとして持つこともできるので、どんな感じか試してます。

まずT-SQLでファイルからDBに登録する方法。OpenRowsetを使います。

SELECT CAST(x as XML) from OpenRowset(BULK 'test.xml' , SINGLE_BLOB) R(x)

テストのために、特定のフォルダにあるxmlファイルを全部登録するためのクエリ。

DECLARE @name varchar(40) , @path varchar(100), @sql varchar(1000)

/* XMLファイルのあるフォルダ */
SET @path = 'C:\data\xml\'

/* ファイル一覧を取得して#filesテーブルに入れる */
CREATE TABLE #files(filename varchar(100))

SET @sql = 'master..xp_cmdshell ''dir /B ' + @path + ' *.xml'''
INSERT INTO #files EXEC(@sql)

DECLARE file_cursor CURSOR FOR SELECT filename FROM #files WHERE filename IS NOT NULL

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		print @path + @name

		SET @sql = 'INSERT INTO xml_files
		select ''' + @name + ''' AS filename, CAST(x as XML) AS contents 
		from OpenRowset(BULK ''' + @path + @name +  ''' , SINGLE_BLOB) R(x)'
		EXEC(@sql);
	END
	FETCH NEXT FROM xml_cursor INTO @name
END

CLOSE file_cursor
DEALLOCATE file_cursor

DROP TABLE #files

xp_cmdshellで"dir"コマンドを実行してデータのあるフォルダのファイル一覧を取得し、#filesという一時テーブルに入れています。
あとxp_cmdshellとOpenRowsetの引数には、変数を入れられないようだったので、文字列でクエリを作成してEXECで実行しています。
xml_filesテーブルはこんな感じです。

CREATE TABLE [dbo].[xml_files](
	[filename] [varchar](50) PRIMARY_KEY,
	[contents] [xml] NULL)

参考