sql – 奇怪的错误XML解析：第1行,字符2038,非法的xml字符

我有这个问题,

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')
            )

这将抛出XML解析：第1行,第2038字符,非法xml字符.当我选择时,

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,ROOT('root')

它显示以下xml,

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

注意#x3.在我的应用程序中,它只是一个空间.这是SQL Server的错误吗？

解决方法

&安培;#X03;是XML中的无效字符.

从Extensible Markup Language (XML) 1.0 (Fifth Edition)起

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

您的查询可以简化为：

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

上述查询的输出是一个包含一行和一列的数据类型为nvarchar(max)的表.

分配给XML变量时会出现错误.

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

或者,当您指定type指令时,该列将是XML列,并且您会收到更详细的错误.

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

Msg 6841,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.

在生成XML之前,必须删除非法字符.

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