我有这个问题,
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>
解决方法
&安培;#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.
declare @XML xml = replace(( select cast(0x3 as char(1)) col for xml raw ),'','')