We all know that in SQL Server using FOR XML PATH statement to be able to query XML data in data generation, the following are examples of some of its applications.
DECLARE @ TempTable DECLARE @ TempTable table ( UserID table (UserID int , UserName int, UserName nvarchar ( 50 )) ; nvarchar (50));
insert insert into @ TempTable into @ TempTable ( UserID , UserName ) (UserID, UserName) values values ( 1 , ' a ' ) (1, 'a')
insert insert into @ TempTable into @ TempTable ( UserID , UserName ) (UserID, UserName) values values ( 2 , ' b ' ) (2, 'b')
select select UserID , UserName UserID, UserName from @ TempTable from @ TempTable FOR FOR XML XML PATH PATH
Run this script will generate the following results:
< row > <Row>
< UserID > 1 </ UserID > <UserID> 1 </ UserID>
< UserName > a </ UserName > <UserName> a </ UserName>
</ row > </ Row>
< row > <Row>
< UserID > 2 </ UserID > <UserID> 2 </ UserID>
< UserName > b </ UserName > <UserName> b </ UserName>
</ row > </ Row>
We can see that the data generated two lines of the two Node, and edit the parameters of PATH:
select select UserID , UserName UserID, UserName from @ TempTable from @ TempTable FOR FOR XML XML PATH ( ' lzy ' ) PATH ('lzy')
Run the script will generate the following results:
< lzy > <Lzy>
< UserID > 1 </ UserID > <UserID> 1 </ UserID>
< UserName > a </ UserName > <UserName> a </ UserName>
</ lzy > </ Lzy>
< lzy > <Lzy>
< UserID > 2 </ UserID > <UserID> 2 </ UserID>
< UserName > b </ UserName > <UserName> b </ UserName>
</ lzy > </ Lzy>
Can see the node becomes , PATH() In fact, PATH () parameter is in brackets the name of the control node, so we can look at if it is an empty string (not no arguments), what would result?
select select UserID , UserName UserID, UserName from @ TempTable from @ TempTable FOR FOR XML XML PATH ( '' ) PATH ('')
This script will generate the implementation of the above results:
< UserID > 1 </ UserID > <UserID> 1 </ UserID>
< UserName > a </ UserName > <UserName> a </ UserName>
< UserID > 2 </ UserID > <UserID> 2 </ UserID>
< UserName > b </ UserName > <UserName> b </ UserName>
So as not to show a higher level node, and we know PATH mode, the column name or column alias is used as XPath expressions to deal with, that is ， Is the name of the column, so do not give a bold experiment with the specified column names and aliases will be like?
select select CAST ( UserID CAST (UserID AS AS varchar ) + '' , UserName + '' varchar) +'', UserName +'' from @ TempTable from @ TempTable FOR FOR XML XML PATH ( '' ) PATH ('')
The results of running the above sentence will be generated
1a2b 1a2b
All data generated a line, but not connected characters, such data may be useless to everyone, you can then change this:
select select CAST ( UserID CAST (UserID AS AS varchar ) + ' , ' , UserName + '' , ' ; ' varchar) + ',', UserName +'', ';' from @ TempTable from @ TempTable FOR FOR XML XML PATH ( '' ) PATH ('')
The results generated
1,a;2,b; 1, a; 2, b;
We now understand it, you can control parameters to generate the results you want, for example:
select select ' { ' + CAST ( UserID '{' + CAST (UserID AS AS varchar ) + ' , ' , ' " ' + UserName + ' " ' , ' } ' varchar) + ',', '"' + UserName + '"', '}' from @ TempTable from @ TempTable FOR FOR XML XML PATH ( '' ) PATH ('')
The results generated
{1,"a"}{2,"b"} {1, "a"} {2, "b"}
Can also generate other formats, you can mix the format they need.
Here is an application of statistics, I hope you can think of more instances of the following applications
DECLARE @ T1 DECLARE @ T1 table ( UserID table (UserID int , UserName int, UserName nvarchar ( 50 ) , CityName nvarchar (50), CityName nvarchar ( 50 )) ; nvarchar (50));
insert insert into @ T1 into @ T1 ( UserID , UserName , CityName ) (UserID, UserName, CityName) values (1, 'a', 'Shanghai')
insert insert into @ T1 into @ T1 ( UserID , UserName , CityName ) (UserID, UserName, CityName) values (2, 'b', 'Beijing')
insert insert into @ T1 into @ T1 ( UserID , UserName , CityName ) (UserID, UserName, CityName) values (3, 'c', 'Shanghai')
insert insert into @ T1 into @ T1 ( UserID , UserName , CityName ) (UserID, UserName, CityName) values (4, 'd', 'Beijing')
insert insert into @ T1 into @ T1 ( UserID , UserName , CityName ) (UserID, UserName, CityName) values (5, 'e', 'Shanghai')
SELECT SELECT B . CityName , LEFT ( UserList , LEN ( UserList ) - 1 ) B. CityName, LEFT (UserList, LEN (UserList) - 1) FROM FROM ( (
SELECT SELECT CityName , CityName,
( SELECT (SELECT UserName + ' , ' UserName + ',' FROM @ T1 FROM @ T1 WHERE WHERE CityName = A . CityName CityName = A. CityName FOR FOR XML XML PATH ( '' )) PATH ('')) AS AS UserList UserList
FROM @ T1 FROM @ T1 A A
GROUP GROUP BY BY CityName CityName
) ) B B
Generate results (user name each city)
Subscribe