SQL Severでxml列のデータを扱う時用メモ

SQL Serverでxmlを扱いたいときにはxqueryを使う必要がある。
変数ではなく、xml列にデータが入っている際の扱いに関して、たまに使うときにわすれてしまうのでメモ

■Bulk InsertするときのCSVの中身
※当たり前だけど、xml列に対してxqueryを投げるために、Bulk Insertするときはxml列のデータ形式は「xml」を指定する必要がある（vacharとかじゃだめ）

id,xml
2,<root><item id="1">1</item><item id="3">2</item><item id="2">3</item><item id="5">4</item></root>
3,<root><item id="1">1</item><item id="4">2</item><item id="2">3</item><item id="6">4</item></root>

■Bulk Insterのためのクエリ

CREATE TABLE src (
  id varchar(33),
  xml xml NULL
);

BULK INSERT src
FROM 'ファイルパス'
WITH
(
   DATAFILETYPE='char',
   FIRSTROW=2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '0x0a'
);

■テーブルの中身

■xml列の中身

<root>
  <item id="1">1</item>
  <item id="3">2</item>
  <item id="2">3</item>
  <item id="5">4</item>
</root>

■取得するためのクエリ

select
    id,
    t.item.query('.') as Item,
    t.item.value('@id','char(50)') as id,
    t.item.value('.','char(50)') as content
from src
cross apply xml.nodes('/root/item') as t(item);

■取得内容