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

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

USE tempdb;GOCREATE 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'.[1]', N'nvarchar(max)'),1, 1, N'');GODROP 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.

You can also do this:

-- BAD:SELECT STUFF((SELECT N',' + yFROM dbo.x FOR XML PATH(N'')),1, 1, N'');-- GOOD:SELECT STUFF((SELECT N',' + yFROM dbo.x FOR XML PATH(N''), TYPE).value('(./text())[1]','varchar(max)'),1, 1, N'');

See this post on Creating concatenated delimited string from a SQL result set and avoid character encoding when using “FOR XML PATH”

An alternate approach would be to rely on concatenation of characters (of course sql is not great with string operations as it is developed to work with set theory)

USE tempdb;GO CREATE TABLE dbo.x ( y NVARCHAR(255) );INSERT dbo.xSELECT 'Sports & Recreation'UNION ALLSELECT 'x >= y'UNION ALLSELECT 'blat'UNION ALLSELECT '<hooah>';DECLARE @delimitedText varchar(max)SET @delimitedText=''SELECT @delimitedText += CASE WHEN LEN(@delimitedText) > 0 THEN +','+ y ELSE y ENDFROM dbo.x SELECT @delimitedTextGODROP TABLE dbo.x;GO