CODEWITHSUNDEEP
sql-serverxmlt-sql
Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Concatenate with FOR XML changes encoding

I have a query which converts a number into a set of ASCII characters and then attempts to concatenate them into a string:

declare @number int = 651854564

;with cte as (
select @number prev_nr
    , cast(char(@number % 256) as nvarchar(100)) nextchar
union all
select prev_nr / 256    prev_nr
    , cast(char((prev_nr / 256) % 256) as nvarchar(100)) nextchar
from cte
where prev_nr <> 0)
select 
    cast(nextchar + '' as nvarchar(100))
from cte
where prev_nr <> 0
for xml path ('');

The characters resulted from splitting the above number to individual characters is:

characters
ä
‚
Ú
&

But when attempting to concatenate them with FOR XML they change encoding so that in the end & is transformed and concatenated into the final result which is: ä‚Ú&amp;

How can I fix this so I can get the correct encoding and generate the ä‚Ú& result?

I've tried converting everything to nvarchar(100) but I have a feeling this has no effect (and actually almost nothing to do with encoding - or at least not helpful in my scenario).

I've also tried the well known STUFF with FOR XML but that didn't really help.

Upvotes: 2

Views: 135

Answers (3)

Demian Berisford-Maynard
Demian Berisford-Maynard

Reputation: 310

If you want to avoid XML encoding, then you could do the following:

SELECT STUFF((SELECT ','+Name AS [text()] FROM #Test FOR XML PATH(''), 
TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') AS 'NameList'

by adding: ,type).value('.', 'VARCHAR(MAX)'),1,1,'') you skip XML encoding.

Upvotes: 0

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

I think the bigger question is: why are you using FOR XML in the first place? XML is not well suited for this task as it has several "special" characters that need to be encoded as they have functional meaning within XML. The list is short, and even changes with context (i.e. attribute vs. element / content) as shown here:

SELECT N'& < > " ?' FOR XML PATH('');
-- &amp; &lt; &gt; " ?


SELECT N'& < > " ?' AS [attr] FOR XML RAW;
-- <row attr="&amp; &lt; &gt; &quot; ?"/>

Given what you are trying to accomplish, you would be better off doing actual string concatenation as follows, and not going anywhere near XML:

DECLARE @number INT = 651854564;
DECLARE @Result NVARCHAR(MAX) = N'';

;WITH cte AS (
  SELECT @number prev_nr
      , CAST(CHAR(@number % 256) AS NVARCHAR(100)) nextchar
  UNION ALL
  SELECT prev_nr / 256    prev_nr
      , CAST(CHAR((prev_nr / 256) % 256) AS NVARCHAR(100)) nextchar
  FROM cte
  WHERE prev_nr <> 0
)
SELECT  @Result += CAST(nextchar + N'' AS NVARCHAR(100))
FROM cte
WHERE prev_nr <> 0;

SELECT @Result;

-- ä‚Ú&

I simply declared @Result NVARCHAR(MAX) = N'', then changed the SELECT to start with @Result +=, and removed the FOR XML.

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Using the type directive in for xml queries

Try this: 

declare @number int = 651854564

;with cte as (
select @number prev_nr
    , cast(char(@number % 256) as nvarchar(100)) nextchar
union all
select prev_nr / 256    prev_nr
    , cast(char((prev_nr / 256) % 256) as nvarchar(100)) nextchar
from cte
where prev_nr <> 0
)

select (select 
    cast(nextchar + '' as nvarchar(100))
from cte
where prev_nr <> 0
for xml path (''), type).value('.','nvarchar(max)')

rextester demo: http://rextester.com/QRUE46541

returns: ä‚Ú&

Upvotes: 5

Related Questions