sql-server – FOR XML无法序列化数据,因为它包含字符(0x0000)

我有一个大查询(如果有必要,我会在这里发布),我收到此错误：

Msg 6841,Level 16,State 1,Line 1
FOR XML could not serialize the
data for node ‘NoName’ because it contains a character (0x0000) which
is not allowed in XML. To retrieve this data using FOR XML,convert it
to binary,varbinary or image data type and use the BINARY BASE64
directive.

我使用FOR XML的唯一部分是：

WHERE 
    (CodFuncionario = Results.CodFuncionario) 
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS [Experiencia]

但是,什么是节点noname？以及如何查找此值：(0x0000)

这是子查询之一(我有FOR XML的唯一部分)：

SELECT 
    [CodFuncionario],STUFF
    (
        (
            SELECT 
                ' / ' + 
            CAST
            (
                [DescFuncao] + '-' + 
                [DescTempoExperiencia] 
                AS VARCHAR(MAX)
            )...
FROM 
    [Linked_Server].db.dbo.tblFuncionarioExperiencia T0
INNER JOIN
    [Linked_Server].db.dbo.tblFuncao T1 On T0.codFuncao = T1.CodFuncao
INNER JOIN
    [Linked_Server].db.dbo.tblTempoExperiencia T2 ON T0.CodTempoExperiencia = T2.CodTempoExperiencia 
WHERE 
   (CodFuncionario = Results.CodFuncionario) 
   FOR XML PATH(''),'') AS [Experiencia]
  FROM 
      [Linked_Server].db.dbo.tblFuncionarioExperiencia Results  
  GROUP BY 
      CodFuncionario) as T2

  On T0.CodFuncionario = T2.CodFuncionario

Left Join...

解决方法

...
SELECT 
    [CodFuncionario],STUFF
    (
        (
            SELECT 
                ' / ' + 
                CAST
                (
                    [DescFuncao] + '-' + 
                    [DescTempoExperiencia] 
                    AS VARCHAR(MAX)
                )...

应该：

...
SELECT 
    [CodFuncionario],STUFF
    (
        (
            SELECT 
                ' / ' + 
                CAST
                (
                    replace -- *** NEW! ***
                    (
                        [DescFuncao] + '-' + 
                        [DescTempoExperiencia],char(0),''
                    ) 
                    AS VARCHAR(MAX)
                )...

（编辑：宣城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!