In one of my tables an end user managed to insert a character 0x02 into a varchar(255) column. At some point in time, from this table xml documents are generated using the for xml path() syntax. For the record with the 0x02 character in it this now generates an error:
".Net SqlClient Data Provider: Msg 6841, Level 16, State 1, Line 22
FOR XML could not serialize the data for node 'v' because it contains a character (0x0002) 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've now hit this error on char(2), but the same error occurs for a lot more characters.
The example below illustrates this by inserting 2 records in a table and creating an xml document for both records. The record with id = 1 is no problem, a valid xml is generated; even the '&' in it is properly escaped in the xml and translated back into a '&' when I retrieve the varchar value for the element. The record with id = 2 can however not be generated into an xml document.
I would expect an escape sequence like '' to be generated for the char(2) character, analogous like the '&' is escaped as '&'.