THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

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

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement