Copy codeThe Code is as follows:
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 FOR XML PATH
Run this script to generate the following results:
Copy codeThe Code is as follows:
<Row>
<UserID> 1 </UserID>
<UserName> a </UserName>
</Row>
<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:
Copy codeThe Code is as follows:
<Lzy>
<UserID> 1 </UserID>
<UserName> a </UserName>
</Lzy>
<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:
Copy codeThe Code is as follows:
<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.
Copy codeThe Code is as follows:
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 = A. CityName for xml path ('') AS UserList
FROM @ T1
Group by CityName
)
Result (User Name of each city)
Beijing B, d
Shanghai a, c, e