FOR XML PATHで改行を含んだ文字列を取得する

以前、行データをカンマ区切り（,）の文字列に変換する方法でFOR XML PATHを使いましたが、出力する文字列に改行コードを含んでいた場合、そのままではSQLSeverで改行を扱う場合のコード「CHAR(13) + CHAR(10)」が「
 」と出力されてしまったので、その対応になります。

テスト用に以下のテーブルとデータを用意します。

テーブル名「example_tbl」

id	name	kind_id
1	A1	1
2	A2	1
3	A3	1
4	B1	2
5	B2	2
6	C1	3
7	NULL	3
8	NULL	3
9	NULL	4
10	NULL	4

テーブル名「example_kind_tbl」

id	name
1	A
2	B
3	C
4	D

上記のデータをを元に、複数のデータ行を改行コード区切りで取得するSQLは以下の通りです。

SELECT
example_kind_tbl.id AS kind_id
,STUFF
(
    (
        SELECT (CHAR(13) + CHAR(10)) + name
        FROM example_tbl
        WHERE example_tbl.kind_id = example_kind_tbl.id
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 2, '') AS data_list

FROM example_kind_tbl

「TYPE」を指定することでFOR XMLの結果の文字列ではなく、xmlデータ型として取得することができ、取得したデータを「value(XQuery, SQLType)」でSQL型の値に変換しています。

valueの第1引数は「XQuery」式なので「.」は self::node() の省略系になります。また、第2引数では変換したいSQL型を指定します。

実行結果は以下の通りです。

kind_id	data_list
1	A1 A2 A3
2	B1 B2
3	C1
4	NULL

ぱっと見は何やっているか理解しにくいですが、FOR XMLで改行を取得する場合はこういうものなんだと思えば、とくに気にならなくなりました。

• FOR XML クエリの TYPE ディレクティブ | Microsoft Docs
• value() メソッド (xml データ型) | Microsoft Docs