奇怪错误XML解析：第1行，第2038字符，非法xml字符

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,

这将抛出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, ELEMENTS, ROOT('root')

It shows the following xml,

它显示以下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?

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

1 个解决方案

#1

 is an invalid character in XML.

是XML中的无效字符。

From Extensible Markup Language (XML) 1.0 (Fifth Edition)

来自可扩展标记语言(XML)1.0(第五版)

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

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

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

When assigning to a XML variable you get an error.

分配给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

消息9420,级别16,状态1,行1 XML解析:第1行,字符16,非法xml字符

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

或者,当您指定type指令时,该列将是一个XML列,您会得到更详细的错误。

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.

Msg 6841,Level 16,State 1,Line 1 FOR XML无法序列化节点'col'的数据,因为它包含XML中不允许的字符(0x0003)。要使用FOR XML检索此数据,请将其转换为二进制,varbinary或图像数据类型,并使用BINARY BASE64指令。

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

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

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

#1