TSQL Reverse FOR XML Encoding

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

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

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.

--something like this?
SELECT * INTO #Names FROM (
   SELECT Name='<>&' UNION ALL
   SELECT Name='ab<>'
) Names;
-- 1)
SELECT STUFF(
    (SELECT ', ' + Name FROM #Names FOR XML PATH(''))
    ,1,2,'');
-- 2)
SELECT STUFF(
    (SELECT ', ' + Name FROM #Names FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)')
    ,1,2,'');
-- 2) is slower but will not return encoded value.

Hope it help.