Application of SQL Server for XML PATH statement---column career

Often in the forum to see the master using the FOR XML path, because it is a search, recorded a detailed use of the method.
Using the FOR XML PATH statement in SQL Server to generate XML data for the queried data, here are some examples of its application.

DECLARE @TempTable table (UserID int, UserName nvarchar (50));
Insert into @TempTable (userid,username) VALUES (1, ' a ')
Insert into @TempTable (Userid,username) VALUES (2, ' B ')

Select Userid,username from @TempTable the for XML path runs this script and produces the following results:<row>
<UserID>1</UserID>
<UserName>a</UserName>
</row>
<row>
<UserID>2</UserID>
<UserName>b</UserName>
</row> you can see two rows of data generated two nodes, modify the parameters of PATH: Select Userid,username from @TempTable for XML PATH (' Lzy ') run the above script again, The following results will be generated:

<lzy>
<UserID>1</UserID>
<UserName>a</UserName>
</lzy>
<lzy>
<UserID>2</UserID>
<UserName>b</UserName>
</lzy> can see the node becomes, in fact, the path () in parentheses in the parameter is the control node name, so you can see if it is an empty string (not without parameters) what will be the result? select Userid,username from @ TempTable for XML PATH (") executes the above script to produce the result:

<UserID>1</UserID>
<UserName>a</UserName>
<UserID>2</UserID>
<UserName>b</UserName> This will not show the ancestor node,as you know, in PATH mode, column or column aliases are treated as XPath expressions, that is, the names of the columns, so it's a bold experiment not to give the specified column names and aliases?

Select CAST (UserID as varchar) + ', UserName + ' from @TempTable for XML PATH (')

Running the above sentence will produce the result

1a2b

All data is generated in one row, and there are no connection characters, so the data may not be useful for everyone, and can be changed:
Select CAST (UserID as varchar) + ', ', UserName + ', '; ' from @TempTable for XML PATH (')
Generate results
1,a;2,b;
Now that you understand it, you can generate the results you want by controlling the parameters, for example:
Select ' {' + CAST (UserID as varchar) + ', ', ' ' ' +username + ' ', '} ' from @TempTable for XML PATH (')
Generate results
{1, "a"} {2, "B"}
You can also generate other formats that you can combine according to the format you want.
Here is a data statistics application, I hope you can think of more applications through the following examples
DECLARE @T1 table (UserID int, UserName nvarchar (), CityName nvarchar (50));
Insert into @T1 (userid,username,cityname) VALUES (1, ' A ', ' Shanghai ')
Insert into @T1 (Userid,username,cityname) VALUES (2, ' B ', ' Beijing ')
Insert into @T1 (Userid,username,cityname) VALUES (3, ' C ', ' Shanghai ')
Insert into @T1 (userid,username,cityname) VALUES (4, ' d ', ' Beijing ')
Insert into @T1 (userid,username,cityname) VALUES (5, ' e ', ' Shanghai ')

SELECT B.cityname,left (Userlist,len (userlist)-1) from (
SELECT CityName,
(SELECT username+ ', ' from @T1 WHERE cityname=a.cityname for XML PATH (")) As UserList
From @T1 A
GROUP by CityName
) B

Build results (user name per city)

Beijing B,d
Shanghai A,c,e

Goto: Mr Bin's Column application of SQL Server for XML PATH statement---column career

Application of SQL Server for XML PATH statement---column career