FOR XML PATH を使用して SQL Server でカンマ区切りリストを作成する方法

FOR XML PATH を使用して SQL Server にカンマ区切りリストを作成します

この例は、FOR XML PATH を使用してテーブルからカンマ区切りのリストを作成する方法を示しています。

<code class="language-sql">SELECT 
    E1.deptno, 
    allemp = Replace ((SELECT E2.ename AS 'data()' 
                       FROM emp AS e2 
                       WHERE e1.deptno = e2.DEPTNO 
                       FOR xml PATH('')), ' ', ', ') 
 FROM EMP AS e1 
 GROUP BY DEPTNO; </code>

詳細説明:

このクエリの中心は、

が実際の XML をどのように処理するかです。単純化された従業員テーブルを考えてみましょう: FOR XML PATH

<code>EmployeeID      Name
1               John Smith
2               Jane Doe</code>

データを XML 形式に変換します。次のクエリを実行します: FOR XML PATH

<code class="language-sql">SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')</code>

は次の XML を生成します:

<code class="language-xml"><employee><employeeid>1</employeeid><name>John Smith</name></employee><employee><employeeid>2</employeeid><name>Jane Doe</name></employee></code>

PATH 句から「Employee」要素を省略すると、外部 XML タグが削除されます。変更されたクエリ:

<code class="language-sql">SELECT  Name
FROM    Employee
FOR XML PATH ('')</code>

は以下を生成します:

<code class="language-xml"><name>John Smith</name><name>Jane Doe</name></code>

元のクエリでは、無効な XML タグを作成しようとすると、列エイリアス「data()」によってエラーが発生しました。この問題を解決するために、相関サブクエリはエラーを隠し、ラベルを取り除き、プレーン テキストを生成します。

<code class="language-sql">SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')</code>

最後に、

はリスト内のスペースをカンマに置き換えます。 REPLACE

改善されたクエリ:

ただし、次の改良されたクエリの方が望ましいです:

<code class="language-sql">SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; </code>

このクエリは

を使用して最初のカンマとスペースを削除し、名前にスペースが含まれている場合のエラーを回避します。列の別名を省略すると、XML タグが作成されなくなります。 STUFF

その他の注意事項:

特殊な XML 文字をエスケープするには、

を .value と組み合わせて使用​​します。 FOR XML PATH

以上がFOR XML PATH を使用して SQL Server でカンマ区切りリストを作成する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。