18

I have a big query (if necessary I will post it here) and I'm getting this error:

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.

The only part I use FOR XML is here:

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

But, what is node noname? and how can I look for this value: (0x0000)

This is one of the subqueries (the only part I have 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(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2, '') AS [Experiencia]
  FROM 
      [Linked_Server].db.dbo.tblFuncionarioExperiencia Results  
  GROUP BY 
      CodFuncionario) as T2

  On T0.CodFuncionario = T2.CodFuncionario

Left Join...
10

The line:

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

Should be:

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

I get the same error when I do this:

DECLARE @foo VARCHAR(32) = CHAR(0); -- 0x0000
SELECT @foo FOR XML PATH, TYPE;

So, find all instances of DescFuncao or DescTempoExperiencia (sorry, you don't use table aliasing, so it's impossible to tell which table they come from) where the contents contain CHAR(0), and fix them. For example:

UPDATE dbo.whatever 
  SET DescFuncao = REPLACE(DescFuncao, CHAR(0), '')
  WHERE DescFuncao LIKE '%' + CHAR(0) + '%';

It's not enough to filter those rows out in your query, because you don't know at what point the XML methods will go to work, but you may also try:

STUFF(( SELECT ' / ' + CAST(REPLACE([DescFuncao] + '-' 
  + [DescTempoExperiencia], CHAR(0), '') AS VARCHAR(MAX))

Of course, fixing the source data once will be much more efficient than running these replace routines every time.

Note, this might not be the only specific character that causes this problem. 0x0001 -> 0x0008 will also generate the same error. So if you have those characters in there too, you should investigate where they are coming from, and fix the source.

  • 1
    What about for data with lots of emojis? – devinbost May 9 '17 at 5:13
10

I tested this with ASCII characters 0-255 and found out that you get this error for characters: 0x0000, 0x0001, 0x0002, 0x0003, 0x0004, 0x0005, 0x0006, 0x0007, 0x0008, 0x000B, 0x000C, 0x000E, 0x000F, 0x0010, 0x0011, 0x0012, 0x0013, 0x0014, 0x0015, 0x0016, 0x0017, 0x0018, 0x0019, 0x001A, 0x001B, 0x001C, 0x001D, 0x001E, 0x001F.

One workaround is to remove , TYPE from your XML statement.

Another way is to remove those characters in the select statement:

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( 
    < YOUR EXPRESSION TO BE CLEANED >
,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),'')

You could also create a function with these replace statements.

  • This workaround idea helped me find where in a large complex common table expression which for xml block was failing with this error. – Keith John Hutchison Mar 31 '17 at 23:30
  • 2
    This script is useful (though painfully slow), but it doesn't work on emoji characters (such as 0xD83D), and I suspect that there might be a lot of them.... at least in my data. :'( Or should I say: 0x1F62D – devinbost May 9 '17 at 5:12
1

Optimizing answer from @jumxozizi by using translate() (SQL Server 2017++). The code below will replace those characters with periods.

declare
    @illegalChars nvarchar(4000) = 
        char(0) + char(1) + char(2) + char(3) + char(4) + char(5) + char(6) + char(7) + char(8) + char(11) + 
        char(12) + char(14) + char(15) + char(16) + char(17) + char(18) + char(19) + char(20) + char(21) + char(22) + 
        char(23) + char(24) + char(25) + char(26) + char(27) + char(28) + char(29) + char(30) + char(31);

select translate(input, @illegalChars, replicate('.', len(@illegalChars))) as Result

To strip them instead, one could translate() them first to char(0) and then wrap that with a replace().

From idea: https://stackoverflow.com/a/55906638/538763

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.