SQLXML without XML encoding?

(select ...from tfor xml path(''), type).value('.', 'nvarchar(max)')

If you specify type as an option to for xml, you can use an XPath query to convert the XML type back to a varchar. With an example table variable:

declare @MyTable table (id int, name varchar(50))insert @MyTable (id, name) select 1, 'Joel & Jeff'union all select 1, '<<BIN LADEN>>'union all select 2, '&&BUSH&&'

One possible solution is:

select  b.txt.query('root').value('.', 'varchar(max)')from    (        select  distinct id        from    @MyTable        ) across apply        (            select  CASE ROW_NUMBER() OVER(ORDER BY id) WHEN 1 THEN ''                         ELSE ', ' END + name        from    @MyTable        where   id = a.id        order by                 id        for xml path(''), root('root'), type        ) b(txt)

This will print:

Joel & Jeff, <<BIN LADEN>>&&BUSH&&

Here's an alternative without XML conversions. It does have a recursive query, so performance mileage may vary. It's from Quassnoi's blog:

;WITH   with_stats(id, name, rn, cnt) AS        (        SELECT  id, name,                ROW_NUMBER() OVER (PARTITION BY id ORDER BY name),                COUNT(*) OVER (PARTITION BY id)        FROM    @MyTable        ),        with_concat (id, name, gc, rn, cnt) AS        (        SELECT  id, name,                CAST(name AS VARCHAR(MAX)), rn, cnt        FROM    with_stats        WHERE   rn = 1        UNION ALL        SELECT  with_stats.id, with_stats.name,                CAST(with_concat.gc + ', ' + with_stats.name AS VARCHAR(MAX)),                with_stats.rn, with_stats.cnt        FROM    with_concat        JOIN    with_stats        ON      with_stats.id = with_concat.id                AND with_stats.rn = with_concat.rn + 1        )SELECT  id, gcFROM    with_concatWHERE   rn = cntOPTION  (MAXRECURSION 0)