XML解析，字符串末尾非法字符

问题描述

当我尝试在 MS SQL Server 中将字符串转换为 XML 时出现非常奇怪的错误:

I'm getting very strange error when I trying to convert a string to XML in MS SQL Server:

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

如果我在文本编辑器中查看字符串，可以看到它的长度是 8070.如果字符 8071 不存在，为什么它会报错?

If I check the string in some text editor, I can see that its length is 8070. Why is it complaining about character 8071 if it does not exist?

这是我将字符串转换为 XML 的方式:

This is how I'm converting string to XML:

CAST(REPLACE(SUBSTRING(
REPLACE(REPLACE(REPLACE(ResponseData,'ä','a'),'ö','o'),'å','a'),
PATINDEX('%<?xml%',ResponseData), PATINDEX('%sonType>', ResponseData)+6),
'<?xml version="1.0" encoding="utf-16"?>',
'<?xml version="1.0" encoding="utf-8"?>')as XML) as ResponseData

是否有任何替换导致问题?

Are any of replaces causing the problem?

UPD:问题还在于，在ResponseData 列中，XML 字符串与一些其他数据一起存储.示例:

UPD: The problem also is that in ResponseData column the XML string is stored together with some other data. Example:

Error from service: <Some error description>. Sent request: <?xml version="1.0" encoding="utf-16"?><Contents of the XML>

所以我需要从列中获取 XML 字符串，然后将其转换为 XML.

So I need to get that XML string from the column and then convert it to XML.

推荐答案

您可以尝试将原始编码从 UTF-16 更改为 ISO-8859-1，或者更精确的字符编码:

You could try to change original encoding from UTF-16 to ISO-8859-1, or a more precise encoding for your characters:

DECLARE @data varchar(max) = '<?xml version="1.0" encoding="utf-16"?><...>'
SELECT CAST(REPLACE(@data,
            '<?xml version="1.0" encoding="utf-16"?>',
            '<?xml version="1.0" encoding="iso-8859-1"?>') AS XML) ResponseData

这篇关于XML解析，字符串末尾非法字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！