Sql-stuff-for-xml comma-separated statements

1.for XML Path ("), stitching multiple rows of data into an XML format substring

2.stuff (string,index,length,otherstring), removes characters of a specified length and inserts another set of characters

Instance:

Creating Tables CREATE table Pro_list (pro_no nvarchar (50))

1.

SELECT distinct top pro_no as Z from pro_list for XML Path (' P ')

Output: <p><pro_no>100009</pro_no></p><p><pro_no>1011010</pro_no></p ><p><pro_no>1011050</pro_no></p><p><pro_no>1011062</pro_no></p ><p><pro_no>1011065</pro_no></p><p><pro_no>1011078</pro_no></p ><p><pro_no>1011081</pro_no></p><p><pro_no>1011088</pro_no></p ><p><pro_no>1011089</pro_no></p><p><pro_no>1011105</pro_no></p >

2.

SELECT distinct top pro_no as Z from pro_list for XML Path (' P ')

Output:

<p><z>100009</z></p><p><z>1011010</z></p><p><z> 1011050</z></p><p><z>1011062</z></p><p><z>1011065</z> </p><p><z>1011078</z></p><p><z>1011081</z></p><p> <z>1011088</z></p><p><z>1011089</z></p><p><z>1011105< /z></p>

3.

SELECT distinct top pro_no as Z from pro_list for XML Path (")

Output:

<z>100009</z><z>1011010</z><z>1011050</z><z>1011062</z><z >1011065</z><z>1011078</z><z>1011081</z><z>1011088</z><z> 1011089</z><z>1011105</z>

4.

Select STUFF ((select DISTINCT top pro_no from Pro_list for XML Path (")), 1, 0, ')

Output:

<Pro_No>100009</Pro_No><Pro_No>1011010</Pro_No><Pro_No>1011050</Pro_No> <Pro_No>1011062</Pro_No><Pro_No>1011065</Pro_No><Pro_No>1011078</Pro_No> <Pro_No>1011081</Pro_No><Pro_No>1011088</Pro_No><Pro_No>1011089</Pro_No> <Pro_No>1011105</Pro_No>

5.

Select STUFF ((select distinct top ', ' +pro_no from Pro_list for XML Path (') '), 1, 1, ')

Output:

100009,1011010,1011050

Sql-stuff-for-xml comma-separated statements