Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

String concatenation in Transact-SQL

4.87/5 (13 votes)
5 Mar 2011CPOL 28.5K  
Performing fast concatenation of strings from different records
Many articles show how to concatenate rowset in single string using FOR XML PATH('') as follows:

SQL
SELECT ProductName + ','
FROM Northwind.dbo.Products
FOR XML PATH('');


The problem here is that XML entities can appear in the result string. If you have "Head & Shoulders" in list of products, you will find "Head & Shoulders" in the resulting string.

The following code performs correct concatenation:

SQL
DECLARE @iXml xml;

SELECT @iXml = (
  SELECT ProductName + ','
  FROM Northwind.dbo.Products
  FOR XML PATH);

SELECT @iXml.value('.','nvarchar(max)');

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)