FOR XML PATH clause in SQL Server 

In SQL Server, we may need to concatenate values from multiple rows into a single string. We can use the XML PATH technique instead because the SQL Server does not have a built-in function to concatenate strings. XML PATH is very efficient. It allows you to combine multiple rows into a single string separated by a comma. 

Table of Contents:

• What is the FOR XML PATH Clause in SQL Server?
• Examples of the FOR XML PATH in SQL Server
• Key Takeaways
• Conclusion

What is the FOR XML PATH Clause in SQL Server?

The FOR XML PATH in SQL Server treats the output as an XML fragment and allows it to merge values efficiently. The FOR XML PATH can handle NULL values and combine multiple columns into a single line of text. 

Syntax:

SELECT column_name + ', ' 
FROM table_name 
FOR XML PATH('');

Examples of the FOR XML PATH in SQL Server

The FOR XML PATH will convert the values into XML fragments, then after the concatenation, it will change the XML fragment into a single line string.

Example 1: To get employees’ names with department details 

-------Create a table named employees
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Department NVARCHAR(50),
    EmployeeName NVARCHAR(100)
);
------Insert the value into employees table 
INSERT INTO Employees (ID, Department, EmployeeName)
VALUES 
    (1, 'HR', 'Kani'),
    (2, 'Content Writer', 'Vasu'),
    (3, 'HR', 'Ravi'),
    (4, 'HR', 'Sri'),
    (5, 'Content Writer', 'Rathore');
-----Query to convert them into strings
DECLARE @EmployeeList NVARCHAR(MAX);
SET @EmployeeList = '';
SELECT SUBSTRING((
    SELECT ', ' + EmployeeName + ' - ' + Department 
    FROM Employees  
    FOR XML PATH('')), 3, 1000) AS [Employee Details];

Output:

Explanation: The SELECT ‘, ‘ + EmployeeName + ‘ – ‘ + Department command changes the strings into XML format. Then XML PATH fetches the data and displays it in a single-line string with department and separated by a comma. 

Example 2: To get the products with their ID and category 

-- Create the Product table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Category NVARCHAR(50),
    ProductName NVARCHAR(100)
);
-- Insert product data
INSERT INTO Products (ProductID, Category, ProductName)
VALUES 
    (1, 'Electronics', 'Smartphone'),
    (2, 'Electronics', 'Laptop'),
    (3, 'Electronics', 'Tablet'),
    (4, 'Furniture', 'Sofa'),
    (5, 'Furniture', 'Dining Table'),
    (6, 'Furniture', 'Chair'),
    (7, 'Clothing', 'T-Shirt'),
    (8, 'Clothing', 'Jeans');
SET QUOTED_IDENTIFIER ON;
SELECT Category, 
       STUFF((
           SELECT ', ' + ProductName  
           FROM Products P 
           WHERE P.Category = C.Category
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ProductList
FROM Products C
GROUP BY Category;

Output:

Explanation: The XML PATH converts all the data into XML format, then converts them into strings. Then using GROUP BY, it segregated the data based on category. 

Key Takeaways

1. XML PATH converts the data into an XML fragment, which helps convert it into strings. 
2. It uses GROUP BY to group the strings in a single line. 
3. It uses custom delimiters “,” to separate the strings.

Conclusion

The XML PATH function is another method to concatenate multiple rows into a single line text string in SQL Server. It is very helpful in grouping a large number of data and is also efficient when we need to summarize a report into a readable format for easier access. The XML is very fast compared to other methods and can also handle the NULL values in a dataset. 

Some Other Methods to Concatenate Text from Multiple Rows into a Single Text String

• Using STRING_AGG() in SQL Server
• Using SQL Server STUFF() Function
• Using COALESCE() Function in SQL Server
• Using CONCAT() function in SQL Server