SQL Server for XML Path statement Application

In SQL Server, the for XML Path statement can be used to generate XML data for the queried data. Below are some examples of its application.

 
Declare @ temptable table (userid int, username nvarchar (50); insert into @ temptable (userid, username) values (1, 'A') insert into @ temp, username) values (2, 'B') Select userid, username from @ temptable for XML Path
 

 
Run this script to generate the following results:
 

 
<Row> <userid> 1 </userid> <username> A </username> </row> <userid> 2 </userid> <username> B </ username> </row>
 

You can see that the two rows of Data generate two nodes and modify the path parameters:
 

 
Select userid, username from @ temptable for XML Path ('lzy ')
 

Run the preceding script again to generate the following results:

 
<Lzy> <userid> 1 </userid> <username> A </username> </lzy> <userid> 2 </userid> <username> B </ username> </lzy>
 

We can see that the parameter in path () is the name of the control node, so you can see what the result will be if it is a null string (not a parameter?
 

 
Select userid, username from @ temptable for XML Path ('')
 

Execute the above script to generate the result:

 
<Userid> 1 </userid> <username> A </username> <userid> 2 </userid> <username> B </username>
 

In this way, the parent node is not displayed. In Path mode, the column name or column alias is processed as an XPATH expression, that is, the column name, in this way, we can boldly test what will happen if we don't specify a column name or alias?

Select cast (userid as varchar) + '', username +'' from @ temptable for XML Path ('')
 

 

Run the above sentence to generate the result

1a2b

All data generates a row without any connection characters. Such data may be of no use and can be changed as follows:

 
Select cast (userid as varchar) + ',', username + '', ';' from @ temptable for XML Path ('')
 

 

Generate results

1, A; 2, B;

Now you understand, you can use the control parameters to generate the desired results, 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, which can be combined according to your needs.

The following is an application of data statistics. I hope you can think of more applications through the following examples.

 

Declare @ T1 table (userid int, username nvarchar (50), 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 =. cityname for XML Path ('') as userlistfrom @ T1 a group by cityname) B
 

 

Result (User Name of each city)

Beijing B, d
Shanghai A, C, E