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

Question

I have this query,

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 select just,

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>

Note the #x3. In my app it's just a space. Is it a bug n SQL Server?

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 output of the above query is a table with one row and one column with the datatype nvarchar(max).

When assigning to a 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 a XML column and you get a more verbose error.

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

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

You have to remove the illegal characters before you generate the XML.

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