2020-01-07 更新

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

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

目次

  • データ
  • コード
  • 実行結果
  • 参考リンク

データ

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

テーブル名「example_tbl」

idnamekind_id
1A11
2A21
3A31
4B12
5B22
6C13
7NULL3
8NULL3
9NULL4
10NULL4

テーブル名「example_kind_tbl」

idname
1A
2B
3C
4D

コード

上記のデータをを元に、複数のデータ行を改行コード区切りで取得する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_iddata_list
1A1 A2 A3
2B1 B2
3C1
4NULL

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

参考リンク

SQL Server】関連記事