与 FOR XML 更改编码连接

Question

我有一个查询，它将一个数字转换为一组ASCII字符，然后尝试将它们连接成一个字符串：

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

将上述数字拆分为单个字符所产生的字符为：

characters
ä
‚
Ú
&

但是当尝试将它们与FOR XML连接起来时，它们会更改编码，以便最终&被转换并连接成最终结果，即：ä‚Ú&

如何解决此问题，以便获得正确的编码并生成ä‚Ú&结果？

我尝试将所有内容转换为nvarchar(100)但我有一种感觉，这没有任何影响(实际上几乎与编码无关 - 或者至少在我的场景中没有帮助(。

我也尝试过众所周知的STUFF FOR XML，但这并没有真正的帮助。

Answer 1

在for xml查询中使用 type 指令

试试这个：

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 演示：http://rextester.com/QRUE46541

返回： ä‚Ú&

Answer 2

我认为更大的问题是：你为什么要首先使用FOR XML？XML 不太适合此任务，因为它有几个需要编码的"特殊"字符，因为它们在 XML 中具有功能含义。该列表很短，甚至会随着上下文(即属性与元素/内容(而变化，如下所示：

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

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

鉴于您要完成的任务，您最好按如下方式进行实际的字符串连接，而不是接近 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;
-- ä‚Ú&

我只是简单地声明了@Result NVARCHAR(MAX) = N''，然后将SELECT改为以@Result +=开头，并删除了FOR XML。

Answer 3

如果要避免 XML 编码，则可以执行以下操作：

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

加入： ，type(.value('.'， 'VARCHAR(MAX('(，1,1，''(跳过 XML 编码。