CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Benny
Benny

Reputation: 3917

TSQL Reverse FOR XML Encoding

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?

Upvotes: 0

Views: 3155

Answers (3)

teamchong
teamchong

Reputation: 1396

--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.

Upvotes: 0

Richard Forss
Richard Forss

Reputation: 670

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.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

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

Upvotes: 4

Related Questions