SQLXML without XML encoding?

Question

I'm using a generic system for reporting which takes data from a database view (SQL Server 2005). In this view I had to merge data from one-to-many relations in one row and used the solution described by priyanka.sarkar in this thread: Combine multiple results in a subquery into a single comma-separated value. The solution uses SQLXML for merging the data (subquery):

SELECT STUFF(
    (    SELECT ', ' + Name 
         FROM MyTable _in 
         WHERE _in.ID = _out.ID 
         FOR XML PATH('')),        -- Output multiple rows as one xml type value,
                                   -- without xml tags
    1, 2, '')      -- STUFF: Replace the comma at the beginning with empty string
FROM MyTable _out 
GROUP BY ID        -- Removes duplicates

That works perfectly (it's not even that heavy in performance) except my data now gets XML encoded (& => & etc.) by SQLXML -I didn't want XML data after all, I just used this as a trick- and because of the generic system I can't code around this to clean it up so the encoded data goes straight to the report. I can't use stored procedures with the generic system so CURSOR-merging or COALESCE-ing is not an option here...

So what I'm looking for is a manner in T-SQL that lets me decode the XML again, or even better: avoids SQLXML from encoding it. Obviously I could write a stored function that does this, but I'd prefer a built-in, more safe manner...

Thanks for your help...

Answer 1

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