SQL for XML clause

Convert the query result to XML format. The last sentence is in the format of 1, 2, and 3. directly add the code without explanation.

1 select * from student for XML Path

Result

<Row>
<ID> 1 </ID>
<Realname> JOHN </realname>
<Sex> 0 </sex>
</Row>
<Row>
<ID> 2 </ID>
<Realname> Xiao Zhang </realname>
<Sex> 0 </sex>
</Row>
<Row>
<ID> 3 </ID>
<Realname> Xiao Chen </realname>
<Sex> 0 </sex>
</Row>
<Row>
<ID> 4 </ID>
<Realname> Zheng </realname>
<Sex> 0 </sex>
</Row>
<Row>
<ID> 5 </ID>
<Realname> Xiao Hei </realname>
<Sex> 0 </sex>
</Row>

2 select * from student for XML raw

Result

<Row id = "1" realname = "John" Sex = "0"/>
<Row id = "2" realname = "John" Sex = "0"/>
<Row id = "3" realname = "Chen" Sex = "0"/>
<Row id = "4" realname = "zheng" Sex = "0"/>
<Row id = "5" realname = "" Sex = "0"/>

3 select * from student for XML raw ('student '), root ('students ')

Result

<Students>
<Student ID = "1" realname = "John" Sex = "0"/>
<Student ID = "2" realname = "John" Sex = "0"/>
<Student ID = "3" realname = "Chen" Sex = "0"/>
<Student ID = "4" realname = "zheng" Sex = "0"/>
<Student ID = "5" realname = "" Sex = "0"/>
</Students>

4 select * from student for XML raw, Elements

Result

<Students>
<Student ID = "1" realname = "John" Sex = "0"/>
<Student ID = "2" realname = "John" Sex = "0"/>
<Student ID = "3" realname = "Chen" Sex = "0"/>
<Student ID = "4" realname = "zheng" Sex = "0"/>
<Student ID = "5" realname = "" Sex = "0"/>
</Students>

5 select stuff (select ',' + realname from student for XML Path (''), 1, 1,'') as names

Result

Xiao Wang, Xiao Zhang, Xiao Chen, Xiao Zheng, Xiao