Stuff 和“For Xml Path”如何在 Sql Server 中工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31211506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Stuff and 'For Xml Path' work in Sql Server
提问by Puneet Chawla
Table is:
表是：
+----+------+
| Id | Name |
+----+------+
| 1 | aaa |
| 1 | bbb |
| 1 | ccc |
| 1 | ddd |
| 1 | eee |
+----+------+
Required output:
所需输出：
+----+---------------------+
| Id | abc |
+----+---------------------+
| 1 | aaa,bbb,ccc,ddd,eee |
+----+---------------------+
Query:
询问：
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
This query is working properly. But I just need the explanation how it works or is there any other or short way to do this.
此查询工作正常。但我只需要解释它是如何工作的，或者是否有任何其他或简短的方法可以做到这一点。
I am getting very confused to understand this.
我对理解这一点感到非常困惑。
回答by FutbolFan
Here is how it works:
下面是它的工作原理：
1. Get XML element string with FOR XML
1. 使用 FOR XML 获取 XML 元素字符串
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:
将 FOR XML PATH 添加到查询的末尾允许您将查询结果作为 XML 元素输出，元素名称包含在 PATH 参数中。例如，如果我们要运行以下语句：
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), we get the following instead:
通过传入一个空白字符串（FOR XML PATH('')），我们得到以下结果：
,aaa,bbb,ccc,ddd,eee
2. Remove leading comma with STUFF
2. 用 STUFF 删除前导逗号
The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
STUFF 语句从字面上将一个字符串“填充”到另一个字符串中，替换第一个字符串中的字符。然而，我们只是使用它来删除结果值列表的第一个字符。
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
The parameters of STUFFare:
的参数STUFF是：
- The string to be “stuffed” (in our case the full list of name with a leading comma)
- The location to start deleting and inserting characters (1, we're stuffing into a blank string)
- The number of characters to delete (1, being the leading comma)
- 要“填充”的字符串（在我们的例子中是带前导逗号的完整名称列表）
- 开始删除和插入字符的位置（1，我们正在填充一个空白字符串）
- 要删除的字符数（1，作为前导逗号）
So we end up with:
所以我们最终得到：
aaa,bbb,ccc,ddd,eee
3. Join on id to get full list
3.加入id获取完整列表
Next we just join this on the list of id in the temp table, to get a list of IDs with name:
接下来，我们只需将其加入临时表中的 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;
And we have our result:
我们有我们的结果：
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Hope this helps!
希望这可以帮助！
回答by Richard Deeming
This articlecovers various ways of concatenating strings in SQL, including an improved version of your code which doesn't XML-encode the concatenated values.
本文介绍了在 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
To understand what's happening, start with the inner query:
要了解发生了什么，请从内部查询开始：
SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE
Because you're specifying FOR XML, you'll get a single row containing an XML fragment representing all of the rows.
因为您指定了FOR XML，所以您将得到一行，其中包含一个表示所有行的 XML 片段。
Because you haven't specified a column alias for the first column, each row would be wrapped in an XML element with the name specified in brackets after the FOR XML PATH. For example, if you had FOR XML PATH ('X'), you'd get an XML document that looked like:
因为您没有为第一列指定列别名，所以每一行都将包含在一个 XML 元素中，名称在FOR XML PATH. 例如，如果您有FOR XML PATH ('X')，您将得到一个如下所示的 XML 文档：
<X>,aaa</X>
<X>,bbb</X>
...
But, since you haven't specified an element name, you just get a list of values:
但是，由于您尚未指定元素名称，因此您只会获得一个值列表：
,aaa,bbb,...
The .value('.', 'varchar(max)')simply retrieves the value from the resulting XML fragment, without XML-encoding any "special" characters. You now have a string that looks like:
在.value('.', 'varchar(max)')简单地检索从所得XML片段的值，而无需编码XML的任何“特殊”字符。您现在有一个如下所示的字符串：
',aaa,bbb,...'
The STUFFfunction then removes the leading comma, giving you a final result that looks like:
STUFF然后该函数删除前导逗号，为您提供如下所示的最终结果：
'aaa,bbb,...'
It looks quite confusing at first glance, but it does tend to perform quite well compared to some of the other options.
乍一看，它看起来很混乱，但与其他一些选项相比，它确实表现得相当好。
回答by Neha Chopra
PATH mode is used in generating XML from a SELECT query
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>
The Output is element-centric XML where each column value in the resulting rowset is wrapped in an row element. Because the SELECT clause does not specify any aliases for the column names, the child element names generated are the same as the corresponding column names in the SELECT clause.
输出是以元素为中心的 XML，其中生成的行集中的每个列值都包含在一个行元素中。由于 SELECT 子句没有为列名指定任何别名，因此生成的子元素名称与 SELECT 子句中对应的列名相同。
For each row in the rowset a tag is added.
对于行集中的每一行，都会添加一个标记。
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>
For Step 2: If you specify a zero-length string, the wrapping element is not produced.
对于第 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
In Step 4 we are concatenating the values.
在第 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
In Step 6 we are grouping the date by ID.
在第 6 步中，我们按 ID 对日期进行分组。
STUFF( source_string, start, length, add_string ) Parameters or Arguments source_string The source string to modify. start The position in the source_string to delete length characters and then insert add_string. length The number of characters to delete from source_string. add_string The sequence of characters to insert into the source_string at the start position.
STUFF( source_string, start, length, add_string ) 参数或自变量 source_string 要修改的源字符串。start 在source_string 中的位置删除length 个字符然后插入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 |
-----------------------------------
回答by Brian Jorden
There is very new functionality in Azure SQL Database and SQL Server (starting with 2017) to handle this exact scenario. I believe this would serve as a native official method for what you are trying to accomplish with the XML/STUFF method. Example:
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
STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx
EDIT:When I originally posted this I made mention of SQL Server 2016 as I thought I saw that on a potential feature that was to be included. Either I remembered that incorrectly or something changed, thanks for the suggested edit fixing the version. Also, pretty impressed and wasn't fully aware of the multi-step review process that just pulled me in for a final option.
编辑：当我最初发布这篇文章时，我提到了 SQL Server 2016，因为我认为我在要包含的潜在功能上看到了这一点。要么我记错了，要么发生了一些变化，感谢建议的编辑修复版本。此外，令人印象深刻的是，并没有完全了解多步骤过程，这只是让我做出最终选择。
回答by vikas
In for xml path, if we define any value like [ for xml path('ENVLOPE') ]then these tags will be added with each row:
在 中for xml path，如果我们定义任何值，[ for xml path('ENVLOPE') ]那么这些标签将与每一行一起添加：
<ENVLOPE>
</ENVLOPE>
回答by SlavaTT
I did debugging and finally returned my 'stuffed' query to it it's normal way.
我进行了调试，最后以正常方式将我的“填充”查询返回给它。
Simply
简单地
select * from myTable for xml path('myTable')
gives me contents of the table to write to a log table from a trigger I debug.
给我表的内容以从我调试的触发器写入日志表。
回答by Mahendra Singh Dhami
SELECT ID,
abc = STUFF(
(SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
)
FROM temp1 GROUP BY id
Here in the above query STUFFfunction is used to just remove the first comma (,)from the generated xml string (,aaa,bbb,ccc,ddd,eee)then it will become (aaa,bbb,ccc,ddd,eee).
在上面的查询中，STUFF函数用于(,)从生成的 xml 字符串中删除第一个逗号，(,aaa,bbb,ccc,ddd,eee)然后它将变为(aaa,bbb,ccc,ddd,eee).
And FOR XML PATH('')simply converts column data into (,aaa,bbb,ccc,ddd,eee)string but in PATHwe are passing '' so it will not create a XML tag.
并FOR XML PATH('')简单地将列数据转换为(,aaa,bbb,ccc,ddd,eee)字符串，但在PATH 中我们传递 '' 因此它不会创建 XML 标记。
And at the end we have grouped records using IDcolumn.
最后，我们使用ID列对记录进行了分组。
回答by Omkar Naik
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
回答by B.Nishan
STUFF((SELECT distinct ',' + CAST(T.ID) FROM Table T where T.ID= 1 FOR XML PATH('')),1,1,'') AS Name
STUFF((SELECT distinct ',' + CAST(T.ID) FROM Table T where T.ID= 1 FOR XML PATH('')),1,1,'') AS Name
回答by sbaysal
Im frequently using with where clause
我经常使用 where 子句
SELECT
TapuAda=STUFF((
SELECT ','+TBL.TapuAda FROM (
SELECT TapuAda FROM T_GayrimenkulDetay AS GD
INNER JOIN dbo.T_AktiviteGayrimenkul AS AG ON D.GayrimenkulID=AG.GayrimenkulID WHERE
AG.AktiviteID=262
) AS TBL FOR XML PATH ('')
),1,1,'')
