SELECT ... FOR XML PATH(' '),1,1) 是什么意思? [英] What is the meaning of SELECT ... FOR XML PATH(' '),1,1)?

问题描述

我正在问题 这里 我看到了这个用法，有人可以让我理解 xml path('') 在 sql 中的含义吗?是的，我浏览了一些我不太了解的网页！

I am learning sql in one of the question and here I 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!

我没有得到后面的东西，现在这段代码有什么作用?(仅 select 部分)

I am not getting the Stuff behind,now what does this piece of code do ?(only select part)

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

推荐答案

这里没有真正的技巧可以学习.将多行数据连接成一个字符串只是一个可爱的技巧.与其说是对 XML 格式化功能的预期用途，不如说是对功能的一种古怪使用.

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 XML formatting feature.

SELECT ',' + ColumnName ... FOR XML PATH('')

基于组合来自 ColumnName 列的多行数据生成一组逗号分隔值.它将产生一个类似 ,abc,def,gjkl 的值.

generates a set of comma separated values, based on combining multiple rows of data from the ColumnName column. It will produce a value like ,abc,def,gjkl.

STUFF(...,1,1,'')

然后用于删除前一个技巧生成的前导逗号，请参阅STUFF 了解其参数的详细信息.

Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.

(奇怪的是，尽管 STUFF 只负责最后一点的修剪，但很多人倾向于将这种生成逗号分隔值集的方法称为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 STUFF only being responsible for a final bit of trimming)

这篇关于SELECT ... FOR XML PATH(' '),1,1) 是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！