SQL Server version of MySQL's group_concat and escaped strings

Question

I only have the Express versions of MS SQL Server 2008 and Visual Studio. Given that I can't create a SQL Server project and therefore CLR solutions are out of the question, I've attempted to use

select col1, stuff( ( select ' ' + col2
from StrConcat t1
where t2.col1 = t1.col1
for xml path('')
),1,1,'')
from StrConcat t2
group by col1
order by col1

to get a row concatenated col2. col2 is a varchar field with some control characters like & and \n. When it is concatenated with the above SQL, it appears to escape those control characters ie. & becomes & amp ; and \n becomes &#xOD, which is not what I want it to do. Given that the col1 and concatenated field is going to be used to update another table, what is the best way to get the concatenated field in its unescaped original form or is there none and the only way is to resort to external code?

The table schema looks like this: StrConcat (id int primary key, col1 int, txt varchar(80)) col1 has an index on it, txt should be grouped by col1, ordered by id within the group.

Answer 1

The behaviour is, of course, by design. If it didn't escape this way, the query could output invalid XML.

If you need to get an unescaped value within SQL Server, then you'll have to use one of the other concatenation methods (recursive CTE is usually the next-most efficient after CLR aggregate and FOR XML, but hardest to write).

Of course, if this is being passed directly to an application, it will be much easier to unescape it there, i.e. using the XmlReader class in .NET.