66

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
Community
  • 1
  • 1
  • 2
    the 1,1) in your question header is `stuff` parameter! stuff takes 4 parameters stuff(@expr,start,len,replacement) so sub query is the expression,starting at positin 1,lenght 1,then replace with `''` – vhadalgi Feb 07 '14 at 10:00
  • Yes. It was confusing.. In lot of places I was seeing this the empty SQL string ('') looks just like it was a single double quote ("), so it looks like you are passing "), 1, 1," to path(). – Darrel Lee May 03 '16 at 02:25

2 Answers2

71

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)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    ok..and what does `FOR XML PATH( '' ) ), 3, 1000 )` mean? –  Feb 07 '14 at 09:28
  • 1
    @george - it's difficult to tell without seeing the larger query. As I hoped was obvious from what I've said in my answer, the `FOR XML PATH('')` and anything that occurs beyond it are part of two *different* tricks or usages, and you need to look earlier in the query to work out what function is being passed the `, 3, 1000` parameters. – Damien_The_Unbeliever Feb 07 '14 at 09:30
31

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
...
Michal Brašna
  • 2,293
  • 13
  • 17