sql 拼接xml

stuff：
1、作用
stuff(param1, startIndex, length, param2)

将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'
select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'
select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'
select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'

1. select stu_name,stu_course from stu_courses for xml path;  

<row>
  <stu_name>张三</stu_name>
  <stu_course>数学</stu_course>
</row>
<row>
  <stu_name>张三</stu_name>
  <stu_course>语文</stu_course>
</row>
<row>
  <stu_name>张三</stu_name>
  <stu_course>英语</stu_course>
</row>
<row>
  <stu_name>李四</stu_name>
  <stu_course>数学</stu_course>
</row>
<row>
  <stu_name>李四</stu_name>
  <stu_course>语文</stu_course>
</row>

select '['+stu_name+','+stu_course+']' from stu_courses for xml path('');  

[张三,数学][张三,语文][张三,英语][李四,数学][李四,语文]