sql2005中一个xml聚合的例子
该问题来自论坛提问，演示SQL代码如下
- --建立测试环境
- setnocounton
- createtabletest(IDvarchar(20),NAMEvarchar(20))
- insertintotestselect'1','aaa'
- insertintotestselect'1','bbb'
- insertintotestselect'1','ccc'
- insertintotestselect'2','ddd'
- insertintotestselect'2','eee'
- go
- --测试
- select*from(selectdistinctidfromtest)a
- OUTERAPPLY(
- selectvalue='<root>'+(
- selectnamefromtestpath
- whereid=A.id
- forxmlauto)+'<root/>')b
- --删除测试环境
- droptabletest
- setnocountoff
- /*--结果
- idvalue
- ----------------------------------------------------------------------------------------
- 1<root><pathname="aaa"/><pathname="bbb"/><pathname="ccc"/><root/>
- 2<root><pathname="ddd"/><pathname="eee"/><root/>
- */