--建立测试环境 set nocount on
create table test(ID varchar(20),NAME varchar(20))
insert into test select ’1’,’aaa’
insert into test select ’1’,’bbb’
insert into test select ’1’,’ccc’
insert into test select ’2’,’ddd’
insert into test select ’2’,’eee’
go
--测试 select *from (select distinct id from test)a
OUTER APPLY(
select value=’<root>’+(
select name from test path
where id = A.id
for xml auto)+’<root/>’) b
--删除测试环境
drop table test
set nocount off
/*--结果
id value
--------------------
1 <root><path name="aaa"/><path name="bbb"/><path name="ccc"/><root/>
2 <root><path name="ddd"/><path name="eee"/><root/>
*/
责任编辑：小草
