How Stuff 'For Xml Path'在SQL Server工作?

这篇文章涵盖了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,...'

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

下面是它的工作原理:

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的参数是:

• 要被“填充”的字符串(在本例中是包含a的name的完整列表) 李领先逗号)< / >
• 开始删除和插入字符的位置(1，我们正在填充到一个空白字符串中)
• 要删除的字符数(1，是前导逗号)

所以我们最终得到:

aaa,bbb,ccc,ddd,eee

3.加入id以获得完整列表

接下来，我们只需将其连接到临时表中的id列表中，以获得带有name的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;

我们得到了结果:

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

<ENVLOPE>
</ENVLOPE>

Azure SQL数据库和SQL Server(从2017年开始)中有非常新的功能来处理这种确切的场景。我相信这可以作为您试图用XML/STUFF方法完成的工作的本地官方方法。例子:

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

String_agg - __abc0

编辑:当我最初发布这篇文章时，我提到了SQL Server 2016，因为我认为我在一个潜在的功能上看到了它。要么是我记错了，要么是发生了什么变化，感谢修改版本的建议编辑。此外，我印象非常深刻，并没有完全意识到让我做出最终选择的多步骤审查过程。

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

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

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

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

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

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>

Output是以元素为中心的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 要修改的源字符串。 开始 在source_string中的位置删除长度字符，然后插入add_string。 长度 要从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 |
-----------------------------------