Click here to Skip to main content
Click here to Skip to main content

Tagged as

String concatenation in Transact-SQL

, 5 Mar 2011 CPOL
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
    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:
 
    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)

Share

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.

Comments and Discussions

 
GeneralMy vote of 5 Pinmembergagan sawaliya18-Dec-12 21:08 
GeneralReason for my vote of 5 I like this solution. Way better tha... PinprotectorAspDotNetDev7-Mar-11 14:09 
GeneralReason for my vote of 5 Elegant and practical solution! 5* PinmemberDrABELL4-Mar-11 7:28 
GeneralReason for my vote of 5 Good one Pinmvpthatraja29-Oct-10 21:03 

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

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

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