How do I avoid character encoding when using "FOR XML PATH"?

Question

I'm looking to create a comma-separated list of values from a SQL Server 2005 table, just like in JanetOhara's question. I'm using a query similar to the one presented in techdo's answer to the question.

Everything is working, except the list of values is getting XML encoded. What should be:

Sports & Recreation,x >= y 

Is instead returning as:

Sports & Recreation,x <= y 

Is there a way to disable the XML character encoding when using "FOR XML" in SQL Server?

Answer 1

You just need to use the right options with FOR XML. Here's one approach that avoids encoding:

USE tempdb; GO  CREATE TABLE dbo.x(y nvarchar(255));  INSERT dbo.x SELECT 'Sports & Recreation'    UNION ALL SELECT 'x >= y'    UNION ALL SELECT 'blat'    UNION ALL SELECT '<hooah>';  -- BAD: SELECT STUFF((SELECT N',' + y   FROM dbo.x    FOR XML PATH(N'')),1, 1, N'');  -- GOOD: SELECT STUFF((SELECT N',' + y   FROM dbo.x    FOR XML PATH,    TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'');  GO DROP TABLE dbo.x; 

If you are on a newer version of SQL Server (2017+), you can use STRING_AGG() and not worry about XML at all:

SELECT STRING_AGG(y, N',') FROM dbo.x; 

db<>fiddle demonstrating all three.