Why does STUFF remove XML?

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?

---

It's not the STUFF , this is only for removing the superfluous first , .

The concat removes the XML stuff:

','+ NAME
or
NAME + ''

Don't ask me why it's working like this, maybe it's documented somewhere :-)

---

Inner for xml statement is just for producing concatenating result. Add outer for xml statement:

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

---

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.

链接地址: http://www.djcxy.com/p/85182.html

上一篇: 表达式树数据结构

下一篇: 为什么STUFF删除XML？