What is the meaning of SELECT ... FOR XML PATH(' '),1,1)?
SqlSql ServerSql Problem Overview
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!
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
Sql Solutions
Solution 1 - Sql
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('')
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,ghi,jkl
.
STUFF(...,1,1,'')
Is then used to remove the leading comma that the previous trick generated, see STUFF
for details about its parameters.
(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)
Solution 2 - Sql
SQL you were referencing is used for string concatenation in MSSQL
.
It concatenates rows by prepending ,
using for xml path
to result
,a,b,c,d
. Then using stuff
it replaces first ,
for
, thus removing it.
The ('')
in for xml path
is used to remove wrapper node, that is being automatically created. Otherwise it would look like <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