Stuff 和“用于 Xml 路径”如何在 SQL Server 中工作？

下面是它的工作原理：

1. 用 FOR XML 获取 XML 元素字符串

将 FOR XML PATH 添加到查询的末尾允许您将查询结果作为 XML 元素输出，元素名称包含在 PATH 参数中。例如，如果我们要运行以下语句：

SELECT ',' + name 
              FROM temp1
              FOR XML PATH ('')

通过传入一个空白字符串 (FOR XML PATH(''))，我们得到以下内容：

,aaa,bbb,ccc,ddd,eee

2. 用 STUFF 去掉前导逗号

STUFF 语句从字面上将一个字符串“填充”到另一个字符串中，替换第一个字符串中的字符。然而，我们只是使用它来删除结果值列表的第一个字符。

SELECT abc = STUFF((
            SELECT ',' + NAME
            FROM temp1
            FOR XML PATH('')
            ), 1, 1, '')
FROM temp1

STUFF 的参数为：

要“填充”的字符串（在我们的例子中是带有前导逗号的完整名称列表）

开始删除和插入字符的位置（1，我们正在填充一个空白字符串）

要删除的字符数（1，作为前导逗号）

所以我们最终得到：

aaa,bbb,ccc,ddd,eee

3.加入id获取完整列表

接下来，我们只需将其加入临时表中的 id 列表，以获取带有名称的 ID 列表：

SELECT ID,  abc = STUFF(
             (SELECT ',' + name 
              FROM temp1 t1
              WHERE t1.id = t2.id
              FOR XML PATH (''))
             , 1, 1, '') from temp1 t2
group by id;

我们有我们的结果：

ID 名称 1 aaa,bbb,ccc,ddd,eee

---

This article 介绍了在 SQL 中连接字符串的各种方法，包括不对连接值进行 XML 编码的代码的改进版本。

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        -- You only want to combine rows for a single ID here:
        WHERE T2.ID = T1.ID
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id

要了解发生了什么，请从内部查询开始：

SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE

因为您指定的是 FOR XML，所以您将获得包含代表所有行的 XML 片段的单行。

因为您没有为第一列指定列别名，所以每一行都将包含在一个 XML 元素中，该元素的名称在 FOR XML PATH 之后的括号中指定。例如，如果您有 FOR XML PATH ('X')，您将获得如下所示的 XML 文档：

<X>,aaa</X>
<X>,bbb</X>
...

但是，由于您没有指定元素名称，因此您只会得到一个值列表：

,aaa,bbb,...

.value('.', 'varchar(max)') 只是从生成的 XML 片段中检索值，而不对任何“特殊”字符进行 XML 编码。你现在有一个看起来像这样的字符串：

',aaa,bbb,...'

然后 STUFF 函数删除前导逗号，最终结果如下所示：

'aaa,bbb,...'

乍一看，它看起来很混乱，但与其他一些选项相比，它确实表现得相当好。

---

PATH 模式用于从 SELECT 查询生成 XML

1. SELECT   
       ID,  
       Name  
FROM temp1
FOR XML PATH;  

Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>

<row>
<ID>1</ID>
<Name>bbb</Name>
</row>

<row>
<ID>1</ID>
<Name>ccc</Name>
</row>

<row>
<ID>1</ID>
<Name>ddd</Name>
</row>

<row>
<ID>1</ID>
<Name>eee</Name>
</row>

输出是以元素为中心的 XML，其中结果行集中的每个列值都包装在一个行元素中。因为 SELECT 子句没有为列名指定任何别名，所以生成的子元素名称与 SELECT 子句中对应的列名相同。

为行集中的每一行添加一个标签。

2.
SELECT   
       ID,  
       Name  
FROM temp1
FOR XML PATH('');

Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>

对于第 2 步：如果指定长度为零的字符串，则不会生成包装元素。

3. 

    SELECT   

           Name  
    FROM temp1
    FOR XML PATH('');

    Ouput:
    <Name>aaa</Name>
    <Name>bbb</Name>
    <Name>ccc</Name>
    <Name>ddd</Name>
    <Name>eee</Name>

4. SELECT   
        ',' +Name  
FROM temp1
FOR XML PATH('')

Ouput:
,aaa,bbb,ccc,ddd,eee

在第 4 步中，我们连接这些值。

5. SELECT ID,
    abc = (SELECT   
            ',' +Name  
    FROM temp1
    FOR XML PATH('') )
FROM temp1

Ouput:
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee
1   ,aaa,bbb,ccc,ddd,eee


6. SELECT ID,
    abc = (SELECT   
            ',' +Name  
    FROM temp1
    FOR XML PATH('') )
FROM temp1 GROUP by iD

Ouput:
ID  abc
1   ,aaa,bbb,ccc,ddd,eee

在第 6 步中，我们按 ID 对日期进行分组。

STUFF( source_string, start, length, add_string ) 参数或参数 source_string 要修改的源字符串。 start source_string 中要删除长度字符的位置，然后插入 add_string。 length 要从 source_string 中删除的字符数。 add_string 要插入到 source_string 开始位置的字符序列。

SELECT ID,
    abc = 
    STUFF (
        (SELECT   
                ',' +Name  
        FROM temp1
        FOR XML PATH('')), 1, 1, ''
    )
FROM temp1 GROUP by iD

Output:
-----------------------------------
| Id        | Name                |
|---------------------------------|
| 1         | aaa,bbb,ccc,ddd,eee |
-----------------------------------

---

Azure SQL 数据库和 SQL Server（从 2017 年开始）中有非常新的功能来处理这种确切的场景。我相信这将作为您尝试使用 XML/STUFF 方法完成的本地官方方法。例子：

select id, STRING_AGG(name, ',') as abc
from temp1
group by id

STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx

编辑：当我最初发布此内容时，我提到了 SQL Server 2016，因为我认为我看到了将要包含的潜在功能。要么我记错了，要么发生了一些变化，感谢修复版本的建议编辑。此外，给我留下了深刻的印象，并且没有完全意识到只是将我拉入最终选择的多步骤审查过程。

---

在 for xml path 中，如果我们定义像 [ for xml path('ENVLOPE') ] 这样的任何值，那么这些标签将添加到每一行：

<ENVLOPE>
</ENVLOPE>

---

SELECT ID, 
    abc = STUFF(
                 (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
               ) 
FROM temp1 GROUP BY id

在上面的查询中，STUFF 函数用于从生成的 xml 字符串 (,aaa,bbb,ccc,ddd,eee) 中删除第一个逗号 (,)，然后它将变为 (aaa,bbb,ccc,ddd,eee)。

FOR XML PATH('') 只是将列数据转换为 (,aaa,bbb,ccc,ddd,eee) 字符串，但在 PATH 中我们传递的是 ''，因此它不会创建 XML 标记。

最后，我们使用 ID 列对记录进行了分组。

---

我进行了调试，最后以正常方式将我的“填充”查询返回给它。

简单地

select * from myTable for xml path('myTable')

给我表的内容以从我调试的触发器写入日志表。

---

Declare @Temp As Table (Id Int,Name Varchar(100))
Insert Into @Temp values(1,'A'),(1,'B'),(1,'C'),(2,'D'),(2,'E'),(3,'F'),(3,'G'),(3,'H'),(4,'I'),(5,'J'),(5,'K')
Select X.ID,
stuff((Select ','+ Z.Name from @Temp Z Where X.Id =Z.Id For XML Path('')),1,1,'')
from @Temp X
Group by X.ID

---