THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The use of FOR XML PATH for string concatenation

I've just read this interesting article from Anith Sen that lists many different ways to concatenate several row values into a single column (i.e. one row for each category with a field containing a comma separated list of products for that category).

I admit I never thought to use the FOR XML PATH solution to get this kind of result. Not only this is a very elegant way to write the query (you formally are not using other recursive approaches based on CTE), but it seems to be also the better solution from a performance point of view. I didn't compared it with the CLR based one, but the performance seems to be so good with the FOR XML PATH solution that I think I will use that by default unless performances are very very critical - I would consider the CLR based solution only if I already have a CLR assembly deployed, but I wouldn't deploy an assembly just for this feature.

Published Wednesday, August 13, 2008 12:09 PM by Marco Russo (SQLBI)



AaronBertrand said:

I wrote about this too, around the time SQL Server 2005 was released:

However, I still think that in many cases it is better to do this concatenation in the front end as opposed to T-SQL.

August 13, 2008 11:40 AM

Marco Russo (SQLBI) said:

Aaron, thank you for your link.

Generally speaking, I agree with you to avoid this kind of transformation in T-SQL. However, if you are building report with Reporting Services, this is the better solution because the alternative in SSRS would be hard to write and to maintain.

August 13, 2008 11:52 AM

AaronBertrand said:

Yes, true.  But I see T-SQL concatenation recommended for the general case (not a reporting services consumer), where it is better done at presentation time.  Note I said "many" and not "all" cases.  :-)

August 13, 2008 12:18 PM

Marco Russo (SQLBI) said:

We completely Agree :)

August 13, 2008 12:19 PM

RJ said:

The XML PATH solution does NOT work in SSRS, at least not 2008, it just shows "Expr" in the output (not the design but in the actual preview). Really annoying actually.

August 31, 2011 10:17 AM

rj said:

If you have a problem with this try modifying the expression in SSRS to something like this:

=TRIM(MID(replace(replace("<" + Fields!YOURFIELDNAME.Value,"<Expr1>",""),"</Expr1>",""),2))

That worked for me.

August 31, 2011 10:34 AM

Erik Eckhardt said:

Rj, You don't have to do those shenanigans if you make the column being stuffed into the XML path have no explicit name. Do something to it to remove the column name such as Value + '' or Convert(datatype, Value). Then your FOR XML PATH ('') will not add the XML tags.

For everyone playing with this be aware that normally escaped characters in XML such as & or CRLF will be escaped in this result...

October 6, 2011 10:08 PM

Hamburgler said:

I am using the XML path solution and it works great when I run it in a SQL editor (TOAD in my case), but when I use it in the query for an SSRS 2005 report, it does not work.

January 26, 2012 12:53 PM

James said:

True that!

July 27, 2012 10:53 AM

Jim Snyder said:

The link is long gone, but the need for in SQL group concatenation remains. I see STUFF used with FOR XML PATH used a lot on the forums, but my coworkers give me blank stares when I mention STUFF. Unfortunately, there is no presentation layer as these are reports that get placed on an sFTP server. Any comments about using STUFF and FOR XML PATH?

February 2, 2017 3:52 PM

Marco Russo (SQLBI) said:

I didn't know the STUFF function! Thanks!

February 15, 2017 12:09 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement