Why does STUFF remove XML?

Question

See below DDL:

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 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 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 to return. However, where are the XML elements left?

Answer 1

This is not STUFF , it is only for removing the extra first,.

Concat removes XML material:

 ','+ NAME or NAME + '' 

Do not ask me why this works this way, perhaps it is documented somewhere :-)

Answer 2

The internal xml statement is only for creating a concatenating result. Add an external xml expression:

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

Output:

  <ID>1</ID><Names>Ian,Mark</Names><ID>2</ID><Names>James</Names><ID>3</ID><Names>Karen,Suzie</Names> 

Fiddle http://sqlfiddle.com/#!6/5f254/13

Answer 3

You have to compare apples with apples. Although it is true that

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

creates something similar to XML (but technically it’s not because it doesn’t have a root element), it (what you are actually doing)

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

not. On my machine, he creates the line ff: "Ian, Mark, James, Karen, Susie." From there, the stuff function starts the first comma, and you get a list of comma-separated values.

Answer 4

Why does STUFF remove XML?

STUFF deletes the first comma in a line; it is not responsible for deleting XML element names.

FOR XML PATH uses column names to create XML element names. When you combine the two values ​​together ','+ NAME , the resulting column has no name, so FOR XML PATH cannot generate the element name for you.

The behavior is documented in Unnamed Columns .

Any column without a name will be embedded. For example, computed columns or nested scalar queries that do not specify a column alias will generate unnamed columns.