sql for xml子句

把查询结果转换成xml格式，最后一句是转换成1,2,3这种形式，不做解释直接上代码。

1 SELECT * FROM student FOR xml path

<row>
  <Id>1</Id>
  <Realname>小王</Realname>
  <Sex>0</Sex>
</row>
<row>
  <Id>2</Id>
  <Realname>小张</Realname>
  <Sex>0</Sex>
</row>
<row>
  <Id>3</Id>
  <Realname>小陈</Realname>
  <Sex>0</Sex>
</row>
<row>
  <Id>4</Id>
  <Realname>小郑</Realname>
  <Sex>0</Sex>
</row>
<row>
  <Id>5</Id>
  <Realname>小黑</Realname>
  <Sex>0</Sex>
</row>

2 SELECT * FROM student FOR xml raw

<row Id="1" Realname="小王" Sex="0"/>
<row Id="2" Realname="小张" Sex="0"/>
<row Id="3" Realname="小陈" Sex="0"/>
<row Id="4" Realname="小郑" Sex="0"/>
<row Id="5" Realname="小黑" Sex="0"/>

3 SELECT * FROM student FOR xml raw('student'),root('students')

<students>
  <student Id="1" Realname="小王" Sex="0"/>
  <student Id="2" Realname="小张" Sex="0"/>
  <student Id="3" Realname="小陈" Sex="0"/>
  <student Id="4" Realname="小郑" Sex="0"/>
  <student Id="5" Realname="小黑" Sex="0"/>
</students>

4 SELECT * FROM student FOR xml raw,elements

<students>
  <student Id="1" Realname="小王" Sex="0"/>
  <student Id="2" Realname="小张" Sex="0"/>
  <student Id="3" Realname="小陈" Sex="0"/>
  <student Id="4" Realname="小郑" Sex="0"/>
  <student Id="5" Realname="小黑" Sex="0"/>
</students>

5 SELECT STUFF((SELECT ',' + Realname FROM student FOR xml path('')),1,1,'') AS Names

小王,小张,小陈,小郑,小黑