将varchar（）中的重音字符转换为XML，导致“非法XML字符” [英] Converting accented characters in varchar() to XML causing "illegal XML character"

问题描述

我有一个应用程序写的表。该字段为varchar（max）。数据看起来像xml。

  DECLARE @poit VARCHAR（100）
 SET @poit ='<？xml version =1.0encoding =utf-8？>< test>VÍA< / test> 
 SELECT CONVERT（XML，@ poit）
  

但是（似乎是因为UTF8;删除它的工作原理），我得到这个错误：

  XML解析：行1，字符46，非法xml字符
  

它？

我发现这个线程，它谈到varchar不支持非ASCII字符，虽然显然我是非unicode。是，我可以这样做：

选择转换（XML，REPLACE（@poit，'encoding =utf-8'，''））

但是这是最好的方法吗？

解决方案

我会尝试更改 @poit 变量 VARCHAR（100）到 NVARCHAR（100）。然后用utf-16替换utf-8编码，这样你的代码看起来像这样：

  DECLARE @poit NVARCHAR 
 SET @poit ='<？xml version =1.0encoding =utf-8？>< test>VÍA< / test>'
 SELECT CONVERT（XML，REPLACE poit，'utf-8'，'utf-16'））
  

在调用结果集的SELECT中，不调用带有替换的转换，性能应该很好，它将完成这项工作。





参考： a href =http://xml.silmaril.ie/characters.html =nofollow> http://xml.silmaril.ie/characters.html < - 向下滚动，看到一些关于utf-8& utf-16。希望这有助于！

I have table written to by an application. The field is varchar(max). The data looks like xml.

DECLARE @poit VARCHAR(100)
SET @poit = '<?xml version="1.0" encoding="utf-8"?><test>VÍA</test>'
SELECT CONVERT(XML,@poit)

But (seemingly because of the UTF8; removing it works), I get this error:

XML parsing: line 1, character 46, illegal xml character

Is there a way to cleanly convert it?

I found this thread, which talks about varchar not supporting "non-ASCII characters", though obviously the I is non-unicode. Yes, I can do this:

SELECT CONVERT(XML, REPLACE(@poit, 'encoding="utf-8"', ''))

But is that the best way?

Why does casting a UTF-8 VARCHAR column to XML require converting to NVARCHAR and encoding change?

解决方案

I would try changing the datatype of your @poit variable from VARCHAR(100) to NVARCHAR(100). Then replace the utf-8 encoding with utf-16 so your code would look something like:

    DECLARE @poit NVARCHAR(100)
    SET @poit = '<?xml version="1.0" encoding="utf-8"?><test>VÍA</test>'
    SELECT CONVERT(XML,REPLACE(@poit, 'utf-8', 'utf-16'))

As long as you're not calling the conversion with the replace in it in a SELECT that returns oodles of results, the performance should be just fine and it will get the job done.

Reference: http://xml.silmaril.ie/characters.html <- scroll down and you'll see some info as to the difference between utf-8 & utf-16. Hope this helps!

这篇关于将varchar（）中的重音字符转换为XML，导致“非法XML字符”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！