Click on “SQL Database Development”,
Set it as “top or star mark” to deliver dry goods in the first time
SQL Server 2017 SQL Server 2017 SQL Server 2017 SQL Server 2017 SQL Server 2017 SQL Server 2017 SQL Server 2017
FOR XML PATH, which handles data merges in older databases, is an old feature that is still available in newer versions. \
What is FOR XML PATH \
FOR XML PATH is to display the query result set as XML, and display the results of multiple rows on the same row.
Let’s use an example to show you how amazing it is.
Create test data
We created a table of students’ hobbies
‘CREATE TABLE Stu_Hobby(Stu_Name NVARCHAR(20),– Age INT,– Age NVARCHAR(20) — Hobby)
INSERT INTO Stu_Hobby VALUES (N ‘zhang, 19, N’ play football), (N ‘Joe’, 19, N ‘basketball’), (N ‘Joe’, 19, N ‘swimming’), (N ‘bill’, 21, N ‘watching movies’), (N ‘bill’, 21, N ‘reading’), (22, N ‘Cathy’, N ‘singing’), (22, N ‘Cathy’, N ‘play’), (N ‘horse six, 19, N’ play football), (N ‘zhao qi, 20, N’ climbing the mountain ‘), (N ‘zhao qi, 20, N’ running ‘) `
(Tip: Slide code left and right)
Select * from Stu_Hobby where Stu_Hobby = Stu_Hobby;
Introduction to Usage
Once the test data is set up, we start querying the data in the table using the FOR XML PATH.
SELECT * FROM dbo.Stu_Hobby FOR XML PATH;
The results are as follows:
It will generate a snippet of XML code, and clicking on this line will bring up a whole XML page, which is a bit too long, so we’ll just take a snippet, as follows:
We can also write parameters after the FOR XML PATH, if followed by a parameter, the node will be replaced by the parameter name, FOR example:
SELECT * FROM dbo.Stu_Hobby FOR XML PATH(hobby)
The results are shown below:
It’s already a parameter that we added.
Close to our actual needs is the following feature
If you want to see what the values of the Hobby column are, you can write:
SELECT * FROM Dbo. Stu_Hobby FOR XML PATH('') SELECT * FROM Dbo. Stu_Hobby FOR XML PATH('')
Note: the + above is field concatenation, which is to concatenate two strings into one string using +. And then let’s get rid of the alpha in XML.
The results are as follows:
So you can see that all of the hobbies that we’ve written are listed, not the ones that are duplicated, so you can kind of visualize all of the values in the column.
Practical application
We now want to display each student’s hobby on a separate line from the student table above, separated by “, “.
SELECT A.Stu_Name, A.Age, (SELECT * FROM [dbo].Stu_Hobby WHERE Stu_Name= a.stu_name AND Age= A.age FOR XML PATH(")) AS Hobby FROM [dbo].Stu_Hobby A GROUP BY A.Stu_Name,A.Age
Time to witness the miracle!! \
Compared to the table we built earlier, we have broken down the data for the Hobby column into a row per student. \
The above WHERE condition is mandatory. What happens if you remove it? Let’s comment out the WHERE condition and see what happens?
SELECT A.Stu_Name, A.Age, (SELECT * FROM [dbo].Stu_Hobby --WHERE --Stu_Name= a.stu_name AND Age= a.age FOR XML PATH(")) AS Hobby FROM [dbo].Stu_Hobby A GROUP BY A.Stu_Name,A.Age
The results are as follows:
All the values of the Hobby column are displayed, which is clearly not what we want
Code optimization
Do you have a “, “at the end of your Hobby column? Do you have a”, “at the end of your Hobby column? The answer is yes.
Start by using a LEFT() and LEN() function to handle the Hobby column
SELECT T.Stu_Name, T.Age, LEFT(T.Hobby,LEN(T.Hobby)-1) AS Hobby FROM (SELECT A.Stu_Name, A.Age, (SELECT * FROM [dbo].Stu_Hobby WHERE Stu_Name= a.stu_name AND Age= A.age FOR XML PATH(")) AS Hobby FROM [dbo].Stu_Hobby A GROUP BY A.Stu_Name,A.Age ) T
The results are as follows:
This is perfect for our needs, but the code is a bit long. Can we make it shorter? The answer is yes! Before we simplify the code, we need to introduce a function that works with it:
****STUFF()
The STUFF() function
The STUFF() function deletes characters of a specified length and can insert another set of characters at a specified starting point. STUFF() returns an empty string if the start position or length value is negative, or if the start position is greater than the length of the first string. If the length to be deleted is greater than the length of the first string, the first character in the first string is deleted.
Syntax for the STUFF() function
STUFF ( character_expression , start , length ,character_expression )
Parameter interpretation
Character_expression: a character data expression. Character_expression can be a constant, variable, character column, or binary data column.
Start: An integer value specifying the starting position for deletion and insertion. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Start can be of type Bigint.
Length: an integer specifying the number of characters to delete. If the length is longer than the first character_expression, a maximum of the last character in the last character_expression can be deleted. Length can be of type Bigint.
The return type
If character_expression is a supported character data type, character data is returned. If character_expression is a supported binary data type, the binary data is returned.
Matters needing attention
1. If the start position or length value is negative, or if the start position is greater than the length of the first string, an empty string is returned. If the length to be deleted is greater than the length of the first string, the first character in the first string is deleted.
2. If the result value is greater than the maximum value supported by the return type, an error is generated.
The above information comes from Microsoft official documentation \
This definition looks dizzy, but let’s see how it works, shall we
Example:
SELECT STUFF('abcdefg',1,1,'1234') -- '1234abcdefg' -- '1234bcdefg' SELECT SELECT STUFF('abcdefg',2,2,'1234') from 'a1234cdefg' from 'a1234defg'
With that said, let’s see how STUFF solves our above problem:
SELECT A.Stu_Name, A.Age, Stu_Hobby FROM [dbo].Stu_Hobby WHERE Stu_Name= a.stu_name AND Age= a.age FOR XML PATH('') Stu_Name= a.age FOR XML PATH('') Stu_Name= a.age FOR XML PATH('') ),1,1, ") AS FROM [dbo].Stu_Hobby A GROUP BY a.st_name, a.age
Is it shorter than LEFT? Let’s see if the result is what we want.
Perfect! \
FOR XML PATH, you can compare the above two optimization methods and compare which method is easier to understand.
Finally, I would like to share with you the PDF electronic version of "SQL Basics 2nd Edition" and "SQL Advanced 2nd Edition". There are various grammar explanations, a large number of examples and annotations, and so on, very easy to understand, convenient for everyone to follow together to practice. There is a need for readers can download learning, in the following public number "data front" (not this number) back keyword: SQL, data front back keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication groupCopy the codeClick “like” and “watching” to tell me you understand ↓
Love you
