加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 黄冈站长网 (http://www.0713zz.com/)- 数据应用、建站、人体识别、智能机器人、语音技术!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

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

发布时间:2021-03-05 19:20:33 所属栏目:MsSql教程 来源:网络整理
导读:我有一个大查询(如果有必要,我会在这里发布),我收到此错误: 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 dat

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

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

(编辑:PHP编程网 - 黄冈站长网)

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

    推荐文章
      热点阅读