我有一个大查询(如果有必要,我会在这里发布),我收到此错误：
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)
)...
