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

发布时间：2023-12-15 03:57:41 所属栏目：MsSql 来源：DaWei

导读： 我有这个问题,DECLARE @Result XML;
SELECT @Result = ( SELECT PD.*
FROM [ProductDetailedDescriptions] PD
LEFT JOIN [Products] P ON (

我有这个问题,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 other</Description> <Order>7</Order> <LanguageID>1</LanguageID> </PD>

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

解决方法 &安培;#X03;是XML中的无效字符.

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

您的查询可以简化为：

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

（编辑：4S站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!