Copy Code code 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
Running this script will produce the following results:
Copy Code code as follows:
<row>
<UserID>1</UserID>
<UserName>a</UserName>
</row>
<row>
<UserID>2</UserID>
<UserName>b</UserName>
</row>
You can see that two rows of data generate two nodes, and modify the path's parameters:
Select Userid,username from @TempTable for XML PATH (' Lzy ')
Running the above script again will produce the following results:
Copy Code code as follows:
<lzy>
<UserID>1</UserID>
<UserName>a</UserName>
</lzy>
<lzy>
<UserID>2</UserID>
<UserName>b</UserName>
</lzy>
You can see that the node becomes, in fact, the parameter in the path () bracket is the control node name, so that you can see what would be the result if it was an empty string (not without parameters)?
Select Userid,username from @TempTable for XML PATH (')
Executing the above script will produce the result:
Copy Code code as follows:
<UserID>1</UserID>
<UserName>a</UserName>
<UserID>2</UserID>
<UserName>b</UserName>
This does not show the ancestor node, as you know in PATH mode, the column name or column alias is treated as an XPath expression, that is, the name of the column, so bold to experiment without giving the specified column name and alias what would it be?
Select CAST (UserID as varchar) + ', UserName + ' from @TempTable for XML PATH (')
Running the above sentence will generate the result
1a2b
All data is generated in one row and there are no connected characters, such data may not be useful to you, you can change it again:
Select CAST (UserID as varchar) + ', ', UserName + ', '; ' from @TempTable for XML PATH (')
Build results
1,a;2,b;
Now that you understand, you can use the control parameters to generate the results you want, such as:
Select ' {' + CAST (UserID as varchar) + ', ', ' ' +username + ' ', '} ' from @TempTable for XML PATH (')
Build 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 use the example below to think of more applications
Copy Code code as follows:
DECLARE @T1 table (UserID int, UserName nvarchar (m), 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
)
Build results (user name for each city)
Beijing B,d
Shanghai A,c,e
