FOR XML 无法序列化 char(0x0000) 即使已经包含 REPLACE 函数来替换 Char(0x0000) [英] FOR XML cannnot serialize char(0x0000) even though REPLACE function already included to replace Char(0x0000)

问题描述

我正在尝试连接每个 ID 的注释字段，以;"分隔从 SQL Server 13 中的每个 ID 将多行转换为一行.同样，我使用以下查询:

I am trying to concatenate comments field per ID separated by ';' from multiple rows into one per ID in SQL Server 13. For the same, I'm using the below query:

--表名中的table1；--element_id 是串联/聚合所需的ID；--value 包含要连接的文本

--table1 in the table name; --element_id is the ID needed for concatenation/aggregation; --value contains the text which is to be concatenated

--值的数据类型为 nvarchar(max)

--Data type of value is nvarchar(max)

       ( SELECT casecomment + ';' 
           FROM (select 
    REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( 
  value
,char(0x0000),'') ,char(0x0001),'') ,char(0x0002),'') ,char(0x0003),'') ,char(0x0004),'') 
,char(0x0005),'') ,char(0x0006),'') ,char(0x0007),'') ,char(0x0008),'') ,char(0x000B),'') 
,char(0x000C),'') ,char(0x000E),'') ,char(0x000F),'') ,char(0x0010),'') ,char(0x0011),'') 
,char(0x0012),'') ,char(0x0013),'') ,char(0x0014),'') ,char(0x0015),'') ,char(0x0016),'') 
,char(0x0017),'') ,char(0x0018),'') ,char(0x0019),'') ,char(0x001A),'') ,char(0x001B),'') 
,char(0x001C),'') ,char(0x001D),'') ,char(0x001E),'') ,char(0x001F),'')
 as casecomment, element_id from table1
where element = 'comments'
) y
          WHERE x.element_id = y.element_id
          ORDER BY element_id
            FOR XML PATH('')  , TYPE).value('.','varchar(max)') as Comments     FROM (select REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 
REPLACE( REPLACE( REPLACE( REPLACE( 
 value
,char(0x0000),'') ,char(0x0001),'') ,char(0x0002),'') ,char(0x0003),'') ,char(0x0004),'') 
,char(0x0005),'') ,char(0x0006),'') ,char(0x0007),'') ,char(0x0008),'') ,char(0x000B),'') 
,char(0x000C),'') ,char(0x000E),'') ,char(0x000F),'') ,char(0x0010),'') ,char(0x0011),'') 
,char(0x0012),'') ,char(0x0013),'') ,char(0x0014),'') ,char(0x0015),'') ,char(0x0016),'') 
,char(0x0017),'') ,char(0x0018),'') ,char(0x0019),'') ,char(0x001A),'') ,char(0x001B),'') 
,char(0x001C),'') ,char(0x001D),'') ,char(0x001E),'') ,char(0x001F),'') as casecomments, element_id from table1

where element = 'comments'
) x
    GROUP BY element_id```

即使我使用了 char(0x0000) 的替换，我仍然收到以下错误:

Even though I have used a replace for char(0x0000), I am still getting the following error:

FOR XML 无法序列化节点NoName"的数据，因为它包含 XML 中不允许的字符 (0x0000).要使用 FOR XML 检索此数据，请将其转换为 binary、varbinary 或 image 数据类型并使用 BINARY BASE64 指令.

我的数据包含许多特殊字符(拉丁字符等)，但我找不到有问题的行.请注意，我有超过 40 万行，因此无法手动评估.另外，我有一个旧版本的 SQL Server，所以翻译功能不起作用.

My data contains many special characters (Latin characters, etc) but I am unable to find the offending row. Please note I have more than 400K rows so it is not possible to evaluate manually. Also, I have an old version of SQL Server, so translate function does not work.

任何帮助将不胜感激.谢谢.

Any help would be appreciated. Thank you.

推荐答案

0 字符 (0x00) 非常特殊...在最低级别它标志着许多环境中字符串的结束.

The 0-character (0x00) is very special... On the lowest level it marks the end of a string in many environments.

试试这个

DECLARE @string VARCHAR(10)=CONCAT('a',CHAR(0),'b');

SELECT LEN(@string) AS LenString
      ,CAST(@string AS VARBINARY(10)) AS Internal
      ,@string AS cut_after_a
      ,CHARINDEX(CHAR(0),@string) AS Pos0_not_found
      ,REPLACE(@string,CHAR(0),'') AS Replace_not_working;

结果

LenString   Internal    cut_after_a Pos0_not_found  Replace_not_working
3           0x610062    a           0               a

但是你可以通过 BIN 排序来解决这个问题

But you can trick this out with a BIN-collation

SELECT LEN(@string) AS LenString
      ,CAST(@string AS VARBINARY(10)) AS Internal
      ,@string COLLATE Latin1_General_BIN AS BIN_but_cut
      ,CHARINDEX(CHAR(0) COLLATE Latin1_General_BIN,@string COLLATE Latin1_General_BIN) AS Pos0_found_at_2
      ,REPLACE(@string COLLATE Latin1_General_BIN,CHAR(0) COLLATE Latin1_General_BIN, '') AS Replace_working;

结果

LenString   Internal    BIN_but_cut Pos0_found_at_2 Replace_working
3           0x610062    a           2               ab

更新:FOR XML 对我有用...

尝试使用 PATH(大多数情况下推荐的方法):

UPDATE: FOR XML works for me...

Try it with PATH (the recommended approach in most cases):

DECLARE @string VARCHAR(10)=CONCAT('a',CHAR(0),'b');

SELECT @string FOR XML PATH('test');

结果

<test>a&#x0;b</test>

您可以阅读此相关问题.

这篇关于FOR XML 无法序列化 char(0x0000) 即使已经包含 REPLACE 函数来替换 Char(0x0000)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！