Bruce W Cassidy (3/19/2009)
One word of warning: the FOR XML will also transform some control characters (such as a carriage return) into the form '& # x 0 D ;'.[/font]
That's know as the "Entitization problem" and there are several ways to address it. Using REPLACE alone is tough because there are a lot of different potential replacements.
The one that I use is to change this:
select
ColA,
stuff((select ', ' + ColB
from @TestTab t2
where t2.ColA = t1.ColA
for xml path('')),1,2,'')
from
@TestTab t1
group by
ColA;
into this:
select
ColA,
stuff( (select ', ' + ColB
from @TestTab t2
where t2.ColA = t1.ColA
for xml path(''), TYPE).value('.[1]', 'varchar(MAX)') ,1,2,'')
from
@TestTab t1
group by
ColA;
This fixes it, but adds some CPU overhead as well.
(edit: forgot the comma between PATH and TYPE...)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]