Query XML structure of ZZ database, application of for XML Path statement

/*
Database Query XML structure, for xml path statement Application

*/

For xml path statement application:

Create table TempTable (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:

<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 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>
<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 upper-level node is not displayed,
--- In PATH mode, the column name or column alias is processed as an XPath expression,
--- That is to say, it is the name of the column, so we can boldly test what will happen if we do not specify the column name or alias?

Select cast (userid as varchar) + ',' + username from TempTable for xml path ('')
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 ('')
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. You can combine them according to the format you need.

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

If OBJECT_ID ('T1 ') is not null drop table t1
Create table t1 (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 ')
--- Generate the following:
/*
Beijing B, d
Shanghai a, c, e
*/
Select * from t1 order by CityName

Select cityname, substring (userlist, 1, LEN (userlist)-1) as userlist
From (
Select distinct cityname,
(
Select username + ', 'From t1 where CityName = a. cityname for xml path ('')
) As userlist
From t1
) B

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
) B

/*
---- Generate result (User Name of each city)

Beijing B, d
Shanghai a, c, e
*/
 
 
 
 
----- Example 2
 
----
 
IF object_id ('T1 ') is not null drop table t1
Create table t1 (eoID int, docID INT, handle VARCHAR (10) --- -- eoID processing organization ID, docID -- processing file ID handle -- processing type
Insert into t1
SELECT 1,100, 'host' UNION ALL
SELECT 3,100, 'will run 'Union ALL
SELECT 2,102, 'host' UNION ALL
SELECT 1,102, 'host' UNION ALL
SELECT 1,103, 'host' UNION ALL
SELECT 3,103, 'host' UNION ALL
SELECT 3,104, 'host' UNION ALL
SELECT 1,104, 'will run 'Union ALL
SELECT 2,104, 'will run 'Union ALL
SELECT 1,105, 'host' UNION ALL
SELECT 3,105, 'will run 'Union ALL
SELECT 2,105, 'Yes'

SELECT * FROM T1 order by eoID

--- 1
SELECT eoid,
Total = COUNT (*),
Host count = sum (case when handle = 'host' THEN 1 ELSE 0 END ),
Organizer = stuff (select cast (docID as varchar) + ', 'From t1 where eoid =. eoID and handle = 'host' for XML path (''), 1, 1 ,''),
Run number = SUM (case when handle = 'Run 'then 1 else 0 end ),
Case = stuff (select CAST (docID as varchar) + ', 'From t1 where eoid =. eoID and handle = 'for XML path (''), 1, 1 ,'')
From t1
Group by eoid
 
 
--- 2
Select eoid, zongshu, SUBSTRING (zhqk, 1, LEN (zhqk)-1), Harvard, SUBSTRING (hbqk, 1, LEN (hbqk)-1)
From
(
SELECT eoid,
Zongshu = COUNT (*),
Zbs = sum (case when handle = 'host' THEN 1 ELSE 0 END ),
Zhqk = (select cast (docID as varchar) + ', 'From t1 where eoid = a. eoID and handle = 'host' for XML path ('')),
Harvard = SUM (case when handle = 'will do 'then 1 else 0 end ),
Hbqk = (select CAST (docID as varchar) + ', 'From t1 where eoid = a. eoID and handle =' for XML path (''))
From t1
Group by eoid
) C
 
 
 
/*
Select * from
(
Select distinct eoid, handle,
(
Select cast (docid as varchar) + ', 'From T1 where eoid = A. eoid and handle = 'host' for XML Path ('')
) As docid
From T1

Union all

Select distinct eoid, handle,
(
Select cast (docid as varchar) + ', 'From T1 where handle = A. Handle and handle =' for XML Path ('')
) As docid
From T1
) C
Where docid is not null
Order by eoid
*/