Click here to Skip to main content
13,195,379 members (59,642 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


12 bookmarked
Posted 28 Oct 2010

String concatenation in Transact-SQL

, 5 Mar 2011
Rate this:
Please Sign up or sign in to vote.
Performing fast concatenation of strings from different records
Many articles show how to concatenate rowset in single string using FOR XML PATH('') as follows:

SELECT ProductName + ','
FROM Northwind.dbo.Products

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:

    DECLARE @iXml xml;
    SELECT @iXml = (
      SELECT ProductName + ','
      FROM Northwind.dbo.Products
      FOR XML PATH);
    SELECT @iXml.value('.','nvarchar(max)');


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


About the Author

Anton Burtsev
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.

You may also be interested in...

Comments and Discussions

PraiseSimple and Practical Pin
jamuro7723-Nov-16 0:36
memberjamuro7723-Nov-16 0:36 
GeneralMy vote of 5 Pin
gagan sawaliya18-Dec-12 20:08
membergagan 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
memberDrABELL4-Mar-11 6:28 
GeneralReason for my vote of 5 Good one Pin
thatraja29-Oct-10 20:03
mvpthatraja29-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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171019.1 | Last Updated 6 Mar 2011
Article Copyright 2010 by Anton Burtsev
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid