【SQLServer】XML型フィールドにスペースありの値を入れる時の注意

XMLでスペースありの値を保持する場合

はまったこと

項目ごとに画面から入力した値をXMLに編集して、SQLServerのXML型フィールドに登録。(機能①)
別の機能(機能②)でこのXML型フィールドを読み込み、画面から入力した値と、XML型フィールドの値を比較して「値の差異あり/なし」で違う処理をしたかったのです。
XMLは↓のような感じ。

<root>
  <field id="項目１">入力</field>
  <field id="項目２">テスト</field>
  <field id="項目３">アウト！</field>
</root>

で、 同じ入力をしたはずなのに、「値の差異あり」の動きをする場合があった。

原因

調査を進めていった結果、機能①、②で共に半角スペース1桁の入力をした場合にこの問題が発生すると判明。
機能①で項目２に半角スペース１桁を入力した場合、登録した時点では↓の通り半角スペースになっているかと思いきや。。。

<root>
  <field id="項目１">入力</field>
  <field id="項目２"> </field>
  <field id="項目３">アウト！</field>
</root>

空タグになっていた。

<root>
  <field id="項目１">入力</field>
  <field id="項目２" />
  <field id="項目３">アウト！</field>
</root>

つまり、機能②で比較をした時、以下の通りとなるために「値の差異あり」の動きをしていたわけです。

比較項目	値
画面から入力した値	半角スペース1桁
XML型フィールドの値	空文字

仕様の確認と動作確認

原因がわかったところで、ちゃんと仕様を追ってみましょう。

XML型の仕様

MicrosoftのXML データのインスタンスの作成を参照。

SQL Server で、要素の内容に含まれる空白文字は、開始タグや終了タグなどのマークアップで区切られた空白文字だけのシーケンス内に出現し、エンティティに変換されていない場合、重要でないと見なされます (CDATA セクションは無視されます)。

ほほう。
さらに読む。

重要ではない空白文字を破棄する条件

• xml:space 属性が、要素またはその先祖の要素で定義されていない。
• 要素またはその先祖の要素の 1 つで有効になっている xml:space 属性に既定値が設定されている。

なるほど。。。
ってことは、xml:space属性を適切に設定してあげれば、問題ないはず。

試してみましょう

テスト用テーブル

IDとXML型フィールド、説明を突っ込むフィールドの3フィールド作成。

CreateTable.sql

CREATE TABLE [dbo].[test_table1](
   [id] [int] NOT NULL,
   [f_xml] [xml] NULL,
   [description] [nvarchar](64) NULL,
CONSTRAINT [PK_test_table1] PRIMARY KEY CLUSTERED
(
   [id] ASC
)
)

データ投入

スペースのパターンを網羅。
そこにxml:space属性を設定しないパターン(1~10)、するパターン(11~20)で登録。

Insert.sql

truncate table test_table1

insert into test_table1 (id, f_xml, [description]) values (1,'<e1><test>123</test></e1>','スペースなし')
insert into test_table1 (id, f_xml, [description]) values (2,'<e1><test></test></e1>','空文字')
insert into test_table1 (id, f_xml, [description]) values (3,'<e1><test> </test></e1>','半角スペース1桁')
insert into test_table1 (id, f_xml, [description]) values (4,'<e1><test>  </test></e1>','半角スペース2桁')
insert into test_table1 (id, f_xml, [description]) values (5,'<e1><test>　</test></e1>','全角スペース1桁')
insert into test_table1 (id, f_xml, [description]) values (6,'<e1><test>　　</test></e1>','全角スペース2桁')
insert into test_table1 (id, f_xml, [description]) values (7,'<e1><test> 456</test></e1>','前スペース')
insert into test_table1 (id, f_xml, [description]) values (8,'<e1><test>567  </test></e1>','後スペース')
insert into test_table1 (id, f_xml, [description]) values (9,'<e1><test>  789  </test></e1>','前後スペース')
insert into test_table1 (id, f_xml, [description]) values (10,'<e1><test>  0 1 2  </test></e1>','前後中間スペース')

insert into test_table1 (id, f_xml, [description]) values (11,'<e1 xml:space="preserve"><test>123</test></e1>','★スペースなし')
insert into test_table1 (id, f_xml, [description]) values (12,'<e1 xml:space="preserve"><test></test></e1>','★空文字')
insert into test_table1 (id, f_xml, [description]) values (13,'<e1 xml:space="preserve"><test> </test></e1>','★半角スペース1桁')
insert into test_table1 (id, f_xml, [description]) values (14,'<e1 xml:space="preserve"><test>  </test></e1>','★半角スペース2桁')
insert into test_table1 (id, f_xml, [description]) values (15,'<e1 xml:space="preserve"><test>　</test></e1>','★全角スペース1桁')
insert into test_table1 (id, f_xml, [description]) values (16,'<e1 xml:space="preserve"><test>　　</test></e1>','★全角スペース2桁')
insert into test_table1 (id, f_xml, [description]) values (17,'<e1 xml:space="preserve"><test> 456</test></e1>','★前スペース')
insert into test_table1 (id, f_xml, [description]) values (18,'<e1 xml:space="preserve"><test>567  </test></e1>','★後スペース')
insert into test_table1 (id, f_xml, [description]) values (19,'<e1 xml:space="preserve"><test>  789  </test></e1>','★前後スペース')
insert into test_table1 (id, f_xml, [description]) values (20,'<e1 xml:space="preserve"><test>  0 1 2  </test></e1>','★前後中間スペース')

結果確認

Display.sql

select a.*,b.id,b.f_xml from
(
select id, f_xml, [description] from test_table1
where id <= 10
) a
inner join
(
select id, id % 10 as modid, f_xml, [description] from test_table1
where id > 10
) b
on a.id = b.modid

a.id	a.f_xml	a.description	b.id	b.f_xml	差異
1	<e1><test>123</test></e1>	スペースなし	11	<e1 xml:space="preserve"><test>123</test></e1>
2	<e1><test /></e1>	空文字	12	<e1 xml:space="preserve"><test /></e1>
3	<e1><test /></e1>	半角スペース1桁	13	<e1 xml:space="preserve"><test> </test></e1>	あり
4	<e1><test /></e1>	半角スペース2桁	14	<e1 xml:space="preserve"><test> </test></e1>	あり
5	<e1><test>　</test></e1>	全角スペース1桁	15	<e1 xml:space="preserve"><test>　</test></e1>
6	<e1><test>　　</test></e1>	全角スペース2桁	16	<e1 xml:space="preserve"><test>　　</test></e1>
7	<e1><test> 456</test></e1>	前スペース	17	<e1 xml:space="preserve"><test> 456</test></e1>
8	<e1><test>567 </test></e1>	後スペース	18	<e1 xml:space="preserve"><test>567 </test></e1>
9	<e1><test> 789 </test></e1>	前後スペース	19	<e1 xml:space="preserve"><test> 789 </test></e1>

半角スペース1桁、2桁の場合で結果が異なった。
スペースが入りうる場合は、ちゃんとxml:space属性をつけましょう。