FOR XML could not serialize the data for node because it contains a character (0x000E)

Question

I am trying to use FOR XML in SSRS, but when the report runs it sometimes gives me this error:

FOR XML could not serialize the data for node 'NoName' because it contains a character (0x000E) 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.

I'm using FOR XML to concatenate a comments column into one cell within SSRS. Since multiple comments can exist for one user, this would solve the issue with duplicates. Does anyone have an idea why I would get this error in SSRS?

Answer 1

It appears to be a special character that looks like a musical note. You can find the row that is causing your problem like this:

SELECT Notes FROM MyTable WHERE Notes like '%' + char(0x000E) + '%'

You can fix the problem by removing the offending character.

 UPDATE MyTable SET Notes = REPLACE(Notes, char(0x000E) , '') 
 WHERE Notes like '%' + char(0x000E) + '%'