Concatenating multiple result rows into a single result without using FOR XML PATH

Question

I've inherited a stored procedure that uses FOR XML PATH in a subselect to concatenate multiple result rows from the subselect into a single result column on the "main" query. Consider the following, where table "z" is part of the main query's FROM clause:

SELECT SUBSTRING((SELECT ('; ' + RTRIM(c.SomeField))
FROM a (NOLOCK)
INNER JOIN b (NOLOCK) ON a.aid = b.aid
INNER JOIN c (NOLOCK) ON b.cid = c.cid
WHERE a.zid = z.zid
FOR XML PATH('')), 3, 1000)

This returns the following, if there were three result rows from the subselect:

value1; value2; value3

Most of the time, this works great. However, several of the values for c.SomeField, which is a varchar, have special XML characters in them, most frequently the ampersand. So, when one of the values has an ampersand, I get this:

value1 & more value1; value2; value3

Is there another way I can concatenate these row results together? If not, is there a simple way to decode the string in .NET?

Answer 1

You can run these statements as one batch to get what you want:

declare @s as varchar(max); 
select @s = isnull(@s + '; ', '') + SomeField
from (
    SELECT RTRIM(c.SomeField) as SomeField
    FROM a (NOLOCK) 
    INNER JOIN b (NOLOCK) ON a.aid = b.aid 
    INNER JOIN c (NOLOCK) ON b.cid = c.cid 
    WHERE a.zid = z.zid 
) a; 
select @s as SomeFieldList; 

Update: here is a proof of concept without using your schema:

declare @s as varchar(max);  
select @s = isnull(@s + '; ', '') + SomeField
from ( 
    SELECT 'aaa' as SomeField
    UNION ALL
    SELECT 'bbb' 
    UNION ALL
    SELECT 'ccc' 
) a;  
select @s as SomeFieldList; 

Answer 2

From MVP Rob Farley:

SELECT SUBSTRING((
  SELECT ('; ' + RTRIM(c.SomeField))
  FROM a (NOLOCK)
  INNER JOIN b (NOLOCK) ON a.aid = b.aid
  INNER JOIN c (NOLOCK) ON b.cid = c.cid
  WHERE a.zid = z.zid
  FOR XML PATH(''), ROOT('xml'), TYPE
).value('/xml[1]','varchar(max)'), 3, 1000)

He points out:

However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.