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

encoding, for-xml-path, sql-server-2005, xml

Solution

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.

Problem

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 &amp; Recreation,x &lt;= y ``` Is there a way to disable the XML character encoding when using "FOR XML" in SQL Server?

Original source

Related problems