How do I avoid character encoding when using "FOR XML PATH"?
You can also do this:
-- 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(N''), TYPE).value('(./text())[1]','varchar(max)'),1, 1, N'');
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.