Why does STUFF remove XML?

Question

Please see the DDL below:

create table #Test (id int,Name varchar(30))

insert into #Test values (1,'Ian')
insert into #Test values(1,'Mark')
insert into #Test values(2,'James')
insert into #Test values(3,'Karen')
insert into #Test values(3,'Suzie')

and the SQL below:

select * from #Test for xml path('')

which returns:

<id>1</id>
<Name>Ian</Name>
<id>1</id>
<Name>Mark</Name>
<id>2</id>
<Name>James</Name>
<id>3</id>
<Name>Karen</Name>
<id>3</id>
<Name>Suzie</Name>

This is what I would expect. Now see the SQL below:

SELECT distinct ID,
STUFF( (select ','+ NAME from #Test as #Test1 where #Test1.id=#Test2.id FOR XML PATH('')),1,1,'') FROM #Test as #Test2

which returns:

1   Ian,Mark
2   James
3   Karen,Suzie

This is what I want returned. However, where have the XML elements gone?

Answer 1

You have to compare apples to apples. While it's true that

select * from #Test for xml path('')

produces something that looks like XML (but technically isn't because it doesn't have a root element), this (what you're actually running)

select ',' + name from #Test for xml path('')

doesn't. On my machine, it produces the ff string: ",Ian,Mark,James,Karen,Suzie". From there, the stuff function whacks the first comma and you get a list of comma-separated values.