如何使用 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>

詳解：

此查詢的核心在於 FOR XML PATH 如何處理實際的 XML。考慮一個簡化的員工表：

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

FOR XML PATH 將資料轉換為 XML 格式。執行以下查詢：

<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>

在原始查詢中，列別名 'data()' 在嘗試建立無效 XML 標籤時會觸發錯誤。為了解決這個問題，相關的子查詢隱藏了錯誤，去除了標籤並產生了純文字。

<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>

此查詢使用 STUFF 刪除第一個逗號和空格，避免了名稱包含空格時的錯誤。省略列別名可防止建立 XML 標籤。

其他注意事項：

為了轉義特殊的 XML 字符，可以使用 .value 與 FOR XML PATH 結合使用：

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

以上是如何使用 FOR XML PATH 在 SQL Server 中建立逗號分隔清單？的詳細內容。更多資訊請關注PHP中文網其他相關文章！