RSS
XML乱舞

.NETでXMLを活用していきます。

SQL ServerでXQueryするには？（準備編3）

»

前回のおさらい

　SQL Serverを使用して、「XQuery用のXML」を取得することに成功。しかし「NULLの項目が出力されない」問題が。試行錯誤の末に「NULLを出力するオプション」をついに発見。「テーブル」→「XML」を実現した。今回は「XML」→「テーブル」に挑戦する。

■TAX（消費税マスタ）テーブル　
TAXCODE TAXNAME TAXRATE MODIFIED
0 非課税 0 2000/01/01 0:00:00
1 課税 5 2001/01/01 0:00:00
8 旧消費税 3 2008/01/01 0:00:00
9 その他 NULL 2009/01/01 0:00:00

■SQL文
1
2
3
4
5		 SELECT
    *
FROM
    TAX
FOR XML PATH, ROOT, ELEMENTS XSINIL

■XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26		 <root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
        <TAXCODE>0</TAXCODE>
        <TAXNAME>非課税</TAXNAME>
        <TAXRATE>0</TAXRATE>
        <MODIFIED>2000-01-01T00:00:00</MODIFIED>
    </row>
    <row>
        <TAXCODE>1</TAXCODE>
        <TAXNAME>課税</TAXNAME>
        <TAXRATE>5</TAXRATE>
        <MODIFIED>2001-01-01T00:00:00</MODIFIED>
    </row>
    <row>
        <TAXCODE>8</TAXCODE>
        <TAXNAME>旧消費税</TAXNAME>
        <TAXRATE>3</TAXRATE>
        <MODIFIED>2008-01-01T00:00:00</MODIFIED>
    </row>
    <row>
        <TAXCODE>9</TAXCODE>
        <TAXNAME>その他</TAXNAME>
        <TAXRATE xsi:nil="true" />
        <MODIFIED>2009-01-01T00:00:00</MODIFIED>
    </row>
</root>

INSERT

　一番最初の「XML」→「テーブル」の例として、まずは「INSERT」からやってみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53		 /* XML文書のハンドル */
DECLARE @hdoc INT


/* XML文書 */
DECLARE @xmltext NVARCHAR(4000)


/* XML文書作成 */
SET @xmltext =
'
<export>
    <data>
        <val1>13</val1>
        <val2>じゅうさん</val2>
        <val3>13</val3>
        <val4>2013/01/01</val4>
    </data>
    <data>
        <val1>14</val1>
        <val2>じゅうよん</val2>
        <val3>14</val3>
        <val4>2014/01/01</val4>
    </data>
</export>
'


/* XML文書のハンドルを取得 */
EXEC sp_xml_preparedocument @hdoc output, @xmltext


/* 実行 */
INSERT INTO
    TAX
SELECT
    ox.val1, /* TAXCODE */
    ox.val2, /* TAXNAME */
    ox.val3, /* TAXRATE */
    ox.val4  /* MODIFIED */
FROM
    OPENXML(@hdoc, N'/export/data', 2)
    WITH (val1 VARCHAR(50), val2 VARCHAR(50), val3 INT, val4 DATETIME) ox
WHERE
    ox.val1 NOT IN (SELECT TAXCODE FROM TAX)


/* XML文書を解放 */
EXEC sp_xml_removedocument @hdoc


/* 実行結果の確認 */
SELECT * FROM TAX

　実行。F5！

TAXCODE TAXNAME TAXRATE MODIFIED
0 非課税 0 2000/01/01 0:00:00
1 課税 5 2001/01/01 0:00:00
13 じゅうさん 13 2013/01/01 0:00:00
14 じゅうよん 14 2014/01/01 0:00:00
8 旧消費税 3 2008/01/01 0:00:00
9 その他 NULL 2009/01/01 0:00:00

　OKです。TAXCODEは文字型なので、「13」と「14」は真ん中にきています。

　それでは、この処理を実際に担当している処理を見ていきます。

XML三人衆

　XMLでテーブルに更新を掛けるには、合計三つの機能が必要です。これを仮に「XML三人衆」と名づけます。

　この三人衆のコンビネーションを順番に見ていきます。

sp_xml_preparedocument

　深い内容は、上のリンクから「MSDN」を直接ご参照いただくとして、簡単な内容を掻い摘んで説明すると、このコラムでは1～30行目の処理になります。このストアドプロシージャが行っていることは、第2引数「xmltext」から受け取ったXML文書をメモリ上に展開し、第1引数「hdoc」にXML文書のハンドルを取得します。

　ハンドルとは、例えば、「車のハンドル」を思い浮かべていただければOKです。「車のハンドル」を握れば、「車」をコントロールできます。同様に、「XML文書のハンドル」を握れば、「XML文書」をコントロールできます。その為に、この「sp_xml_preparedocument」というストアドプロシージャが必要なのです。

　実は、「sp_xml_preparedocument」の処理には、少しコツが必要です。それは、第2引数である「xmltext」についてです。ここには、「char、nchar、varchar、nvarchar、text、ntext、または xml 型」が入ります。しかし、日本語を正しく処理できるのは、「nchar、nvarchar、ntext、xml型」でした（SQL Serverのある設定をいじれば、対応可能なのかもしれませんが……）。

　日本語を正しく処理できる「nchar、nvarchar、ntext、xml型」の中で、私のオススメは「nvarchar型」です。理由としては、「nvarchar型」が一時的な利用にちょうど良いサイズだからです。本来ここはXMLの処理なので、「xml型」でいくべきだと考えたのですが、「xml型」は直接手書きできないという制限がありました。「xml型」を作成するには、テーブルに対して「FOR XML」をつけてSELECTするか、一度「nvarchar型」などでXMLを記述してから、「xml型」にキャストする必要がありました。

　そもそも「sp_xml_preparedocument」の第2引数には、「nvarchar型」でOKなのですから、わざわざ「xml型」にこだわる必要はなかったのです。ここは是非、「nvarchar型」を使用してください。

OPENXML

　XMLを動的なビューとして表現します。42～43行目が特に重要な部分です。第1引数は、先ほど取得したXML文書のハンドル「@hdoc」を設定します。第2引数は、実データへのXPATHを記述します。「N'/export/data'」を設定します。頭の「N」は日本語の混じったXPATH対応用です。今回は必要ありませんが、念のため入れています。第3引数は、XMLの文書タイプです。今回の文書タイプは、「要素」なので「2」を設定します。

　最後に、「WITH」に続けてスキーマを記述します。SQL Serverには暗黙変換の機能がありますが、対応するフィールドと同じ型に揃えておきます。

TAXCODE　VARCHAR(50) → val1 VARCHAR(50)
TAXNAME　VARCHAR(50) → val2 VARCHAR(50)
TAXRATE　INT → val3 INT
MODIFIED　DATETIME → val4 DATETIME

sp_xml_removedocument

　メモリ上に展開されているXML文書を解放します。第1引数に「sp_xml_preparedocument」で取得しておいたXML文書のハンドル「@hdoc」を指定します。これを忘れると、SQL Serverがメモリ不足になることがあるそうです。49行目にこの処理を行っています。

　以上で、コンビネーションの説明は終わりです。お疲れ様でした。

　さてさて、XMLで「INSERT」ができるならば、「UPDATE」も可能なのでしょうか？ その答えは「Yes」です。

UPDATE

　INSERTの場合と、やっていることは同じです。例として「TAXNAME」を漢字にしてみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48		 /* XML文書のハンドル */
DECLARE @hdoc INT


/* XML文書 */
DECLARE @xmltext NVARCHAR(4000)


/* XML文書作成 */
SET @xmltext =
'
<export>
    <data>
        <val1>13</val1>
        <val2>十三</val2>
    </data>
    <data>
        <val1>14</val1>
        <val2>十四</val2>
    </data>
</export>
'


/* XML文書のハンドルを取得 */
EXEC sp_xml_preparedocument @hdoc output, @xmltext


/* 実行 */
UPDATE
    TAX
SET
    TAX.TAXCODE = ox.val1,
    TAX.TAXNAME = ox.val2
FROM
    OPENXML(@hdoc, N'/export/data', 2)
WITH (val1 VARCHAR(50), val2 VARCHAR(50)) ox
WHERE
    TAX.TAXCODE = ox.val1


/* XML文書を解放 */
EXEC sp_xml_removedocument @hdoc


/* 実行結果の確認 */
SELECT * FROM TAX

　実行。F5！

TAXCODE TAXNAME TAXRATE MODIFIED
0 非課税 0 2000/01/01 0:00:00
1 課税 5 2001/01/01 0:00:00
13 十三 13 2013/01/01 0:00:00
14 十四 14 2014/01/01 0:00:00
8 旧消費税 3 2008/01/01 0:00:00
9 その他 NULL 2009/01/01 0:00:00

　できました。

　主キーの「TAXCODE」と、更新対象の「TAXNAME」の分だけXMLを用意すればOKです。

DELETE

　最後に「DELETE」です。「INSERT」した「13」と「14」のレコードを削除します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42		 /* XML文書のハンドル */
DECLARE @hdoc INT


/* XML文書 */
DECLARE @xmltext NVARCHAR(4000)


/* XML文書作成 */
SET @xmltext =
'
<export>
    <data>
        <val1>13</val1>
    </data>
    <data>
        <val1>14</val1>
    </data>
</export>
'


/* XML文書のハンドルを取得 */
EXEC sp_xml_preparedocument @hdoc output, @xmltext


/* 実行 */
DELETE
    TAX
FROM
    OPENXML(@hdoc, N'/export/data', 2)
WITH (val1 VARCHAR(50)) ox
WHERE
    TAX.TAXCODE = ox.val1


/* XML文書を解放 */
EXEC sp_xml_removedocument @hdoc


/* 実行結果の確認 */
SELECT * FROM TAX

　実行。F5！

TAXCODE TAXNAME TAXRATE MODIFIED
0 非課税 0 2000/01/01 0:00:00
1 課税 5 2001/01/01 0:00:00
8 旧消費税 3 2008/01/01 0:00:00
9 その他 NULL 2009/01/01 0:00:00

　テーブルが最初の状態に戻りましたね。

ありがとうございました

　さて、これで「XML」→「テーブル」を一通り行ったことになります。「XML三人衆」さえ使いこなせれば、普通のT-SQLの処理なんだなーと感じました。

　今回の「SQL ServerでXQueryするには？（準備編3）」をもって、準備編は終了です。そして、XQueryの次のテーマは、「FLWOR式」です。しかし、.NET技術者としては、「Linq To SQL」や「Linq To XML」という技術もあります。正直悩ましいところです。

　とりあえず、次回からは「XQuery」とは別の話題でいきたいと思っています。

はやしさとし 2009/05/07 15:55:00 Comment(0)

コメント

コメントを投稿する