SQL SELECT ... FOR XML PATH(' '),1,1) 是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21623593/
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
What is the meaning of SELECT ... FOR XML PATH(' '),1,1)?
提问by Damien_The_Unbeliever
I am learning sql in one of the questionand hereI saw usage of this,can some body make me understand what xml path('') mean in sql? and yes,i browsed through web pages I didn't understand it quite well!
我在其中一个问题中学习 sql，在这里我看到了这个用法，有人可以让我理解 xml path('') 在 sql 中的含义吗？是的，我浏览了一些我不太理解的网页！
I am not getting the Stuff behind,now what does this piece of code do ?(only selectpart)
我没有得到后面的东西，现在这段代码有什么作用？（仅select部分）
declare @t table
(
Id int,
Name varchar(10)
)
insert into @t
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 2,'d'
select ID,
stuff(
(
select ','+ [Name] from @t where Id = t.Id for XML path('')
),1,1,'')
from (select distinct ID from @t )t
回答by Damien_The_Unbeliever
There's no real technique to learn here. It's just a cute trick to concatenate multiple rows of data into a single string. It's more a quirky use of a feature than an intended use of the XMLformatting feature.
这里没有真正的技术可以学习。将多行数据连接成一个字符串只是一个可爱的技巧。与其说是XML格式化功能的预期用途，不如说是对功能的一种古怪使用。
SELECT ',' + ColumnName ... FOR XML PATH('')
generates a set of comma separated values, based on combining multiple rows of data from the ColumnNamecolumn. It will produce a value like ,abc,def,ghi,jkl.
基于组合列中的多行数据，生成一组逗号分隔值ColumnName。它会产生一个像,abc,def,ghi,jkl.
STUFF(...,1,1,'')
Is then used to remove the leading comma that the previous trick generated, see STUFFfor details about its parameters.
然后用于删除前一个技巧生成的前导逗号，STUFF有关其参数的详细信息，请参阅。
(Strangely, a lot of people tend to refer to this method of generating a comma separated set of values as "the STUFF method" despite the STUFFonly being responsible for a final bit of trimming)
（奇怪的是，很多人倾向于将这种生成逗号分隔值集的方法称为“STUFF 方法”，尽管STUFF它只负责最后一点的修剪）
回答by Michal Bra?na
SQL you were referencing is used for string concatenation in MSSQL.
您引用的 SQL 用于MSSQL.
It concatenates rows by prepending ,using for xml pathto result
,a,b,c,d. Then using stuffit replaces first ,for , thus removing it.
它通过预先,使用for xml path结果来
连接行,a,b,c,d。然后使用stuff它首先替换,for ，从而将其删除。
The ('')in for xml pathis used to remove wrapper node, that is being automatically created. Otherwise it would look like <row>,a,b,c,d</row>.
的('')在for xml path用于去除包装节点，正被自动创建。否则它看起来像<row>,a,b,c,d</row>.
...
stuff(
(
select ',' + CAST(t2.Value as varchar(10)) from #t t2 where t1.id = t2.id
for xml path('')
)
,1,1,'') as Value
...
- more on stuff
- more on for xml path
