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 &amp; Recreation,x &lt;= y

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

Aaron Bertrand · Accepted Answer · 2022-01-24T22:07:45.513

146

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.

edited Jan 24 '22 at 22:07

answered Mar 26 '13 at 17:35

Aaron Bertrand

272,866
37
466
490

14

Adding `type` and `.value('.[1]',nvarchar(max)')` worked perfectly. Thanks for the help. – dangowans Mar 26 '13 at 17:45
11

TYPE tells SQL to return actual XML, then you have to "Query" this XML for the value you want this is the ".value('.[1]', nvarchar(max)'). More info here: http://technet.microsoft.com/en-us/library/ms190025.aspx – ProVega Oct 09 '13 at 04:11
1

The first character in my string was a '<', this was for some reason omitted after implementing this solution. I solved that issue by adding a blank space at the beginning of the string. – alan Apr 02 '15 at 14:05
@alan I couldn't reproduce that problem. Did you change `',' + y` to something else? The `STUFF` is used to strip off the first character, which should be a leading comma unless you changed the query in other ways. – Aaron Bertrand Apr 02 '15 at 14:09
3

Worth noting that I found `value` to be case-sensitive, it wouldn't recognise `VALUE`. – Andy G Aug 20 '18 at 11:17
STRING_AGG is worth a look as an alternative to this. MS example: ```SELECT town, STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails FROM dbo.Employee GROUP BY town;``` – Nick Allan Feb 03 '19 at 11:34
@NickAllan However note that "STRING_AGG" Is only for SQL 2017+ which isn't much use to anyone who support older systems, but thanks for the heads up. – Harag Nov 23 '20 at 14:39

score 2 · Answer 2 · answered Sep 28 '20 at 14:54

2

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'');

answered Sep 28 '20 at 14:54

Leonel Gonzalez

89
7

score 0 · Answer 3 · edited Dec 22 '17 at 19:14

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.x
SELECT 'Sports & Recreation'
UNION ALL
SELECT 'x >= y'
UNION ALL
SELECT 'blat'
UNION ALL
SELECT '<hooah>';

DECLARE @delimitedText varchar(max)
SET @delimitedText=''
SELECT @delimitedText += CASE WHEN LEN(@delimitedText) > 0 THEN +','+ y ELSE y END
FROM dbo.x 

SELECT @delimitedText
GO
DROP TABLE dbo.x;
GO

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

3 Answers3

Linked

Related