Click here to Skip to main content
15,867,895 members
Articles / Database Development / SQL Server
Tip/Trick

String concatenation in Transact-SQL

Rate me:
Please Sign up or sign in to vote.
4.87/5 (13 votes)
5 Mar 2011CPOL 27.3K   12   5
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)


Written By
Web Developer
Russian Federation Russian Federation
I have started as a C++ developer in far 2000. I'd been developing banking and treasury software for 3 years using C++. In 2003 I switched to .NET at DELL. After that I worked as a project manager on different projects (internal audit, treasury automation, publishing house automation, etc.). Since 2009 I own a small software company specialized in SaaS services and develop a DotNetNuke modules.

Comments and Discussions

 
PraiseSimple and Practical Pin
jamuro7723-Nov-16 0:36
jamuro7723-Nov-16 0:36 
GeneralMy vote of 5 Pin
gagan sawaliya18-Dec-12 20:08
gagan sawaliya18-Dec-12 20:08 
GeneralReason for my vote of 5 I like this solution. Way better tha... Pin
AspDotNetDev7-Mar-11 13:09
protectorAspDotNetDev7-Mar-11 13:09 
GeneralReason for my vote of 5 Elegant and practical solution! 5* Pin
DrABELL4-Mar-11 6:28
DrABELL4-Mar-11 6:28 
GeneralReason for my vote of 5 Good one Pin
thatraja29-Oct-10 20:03
professionalthatraja29-Oct-10 20:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.