Strange Error XML parsing: line 1, character 2038, illegal xml character

Question

I have this request,

DECLARE @Result XML; SELECT @Result = ( SELECT PD.* FROM [ProductDetailedDescriptions] PD LEFT JOIN [Products] P ON (PD.ProductID= P.ID) WHERE PD.ProductID = 23 AND P.RetailerID = 1 AND PD.LanguageID = 1 ORDER BY [ORDER] FOR XML AUTO, ELEMENTS, ROOT('root') ) 

This throws XML parsing: line 1, character 2038, illegal xml character . When I choose simply,

 SELECT PD.* FROM [ProductDetailedDescriptions] PD LEFT JOIN [Products] P ON (PD.ProductID= P.ID) WHERE PD.ProductID = 23 AND P.RetailerID = 1 AND PD.LanguageID = 1 ORDER BY [ORDER] FOR XML AUTO, ELEMENTS, ROOT('root') 

It shows the following xml,

 <root> .............................................. .............................................. <PD> <ID>4187</ID> <ProductID>23</ProductID> <Header>aa</Header> <Description>with &#x3;other</Description> <Order>7</Order> <LanguageID>1</LanguageID> </PD> 

Pay attention to # x3 . In my application, this is just a space. Is this a SQL Server error?

Answer 1

 is an invalid character in XML.

From Extensible Markup Language (XML) 1.0 (fifth edition)

Char :: = # x9 | #xA | #xD | [# x20- # xD7FF] | [# xE000- # xFFFD] | [# X10000- # x10FFFF]

Your query that works can be simplified to this:

 select cast(0x3 as char(1)) col for xml raw 

The result of the above query is a table with one row and one column with data type nvarchar(max) .

When you assign an XML variable, you get an error.

 declare @XML xml = ( select cast(0x3 as char(1)) col for xml raw ) 

Msg 9420, level 16, state 1, line 1 XML parsing: line 1, character 16, illegal xml character

Or, when you specify the type directive, the column will be an XML column and you will get a more detailed error.

 select cast(0x3 as char(1)) col for xml raw, type 

Msg 6841, Level 16, State 1, Line 1 FOR XML cannot serialize data for node 'col' because it contains a character (0x0003) that 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.

You must remove invalid characters before creating XML.

 declare @XML xml = replace(( select cast(0x3 as char(1)) col for xml raw ), '', '')