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