23

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...
Improve this question
0

5 Answers 5

Reset to default
13

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)
                )...
Improve this answer
0
12

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.

Improve this answer
0
11

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.

Improve this answer
1
  • 3
    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
    Commented May 9, 2017 at 5:12
4

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

Improve this answer
0

Just to provide a full example of what crokusek suggested to optimize jumxozizi's even more original suggestion.

This carries out the combined suggestion of replace and translate together.

In my example, I had a field where I knew I wanted to find and replace two of the "illegal" characters in the column description. So, it's not completely related to the OP's post, but this is why I ended up here and hopefully this fleshing out of crokusek's answer is helpful!

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 TOP (100) 
       [id]
      ,replace(
        translate(
         description, 
         @illegalChars, 
         replicate(CHAR(0), len(@illegalChars))
        ), 
        CHAR(0), 
        ''
       )
  FROM <table name here>
  WHERE 
     (RTRIM(description) LIKE '%' + CHAR(2) +  '%') 
  OR (RTRIM(description) LIKE '%' + CHAR(11) +  '%');
Improve this answer
1
  • why not provide a good example by creating a table and using your script against it? how you envision the table structure of <table name here> ? just a comment
    – Marcello Miorelli
    Commented May 8 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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