TSQL Reverse FOR XML Encoding

Question

I am using FOR XML in a query to join multiple rows together, but the text contains quotes, "<", ">", etc. I need the actual character instead of the encoded value like """ etc. Any suggestions?

Answer 1

Basically what you're asking for is invalid XML and luckly SQL Server will not produce it. You can take the generated XML and extract the content, and this operation will revert the escaped characters to their text representation. This revert normally occurs in the presnetaitonlayer, but it can occur in SQL Server itslef by instance using XML methods to extract the content of the produced FOR XML output. For example:

declare @text varchar(max) = 'this text has < and >';
declare @xml xml;

set @xml = (select @text as [node] for xml path('nodes'), type);

select @xml;
select x.value(N'.', N'varchar(max)') as [text]
from @xml.nodes('//nodes/node') t(x);

Answer 2

I have a similar requirement to extract column names for use in PIVOT query.

The solution I used was as follows:

SELECT @columns = STUFF((SELECT '],[' + Value
                               FROM Table
                              ORDER BY Value
                                FOR XML PATH('')), 1, 2, '') + ']'

This produces a single string:

[Value 1],[Value 2],[Value 3]

I hope this points you in the right direction.