我有一个由应用程序写入的表格。该字段是varchar(max)类型。数据看起来像是xml。
DECLARE @poit VARCHAR(100)
SET @poit = '<?xml version="1.0" encoding="utf-8"?><test>VÍA</test>'
SELECT CONVERT(XML,@poit)
XML parsing: line 1, character 46, illegal xml character
有没有一种干净的方法可以进行转换？
我发现这个线程，它讨论了varchar不支持“非ASCII字符”，尽管显然I是非Unicode的。 是的，我可以这样做：
SELECT CONVERT(XML，REPLACE（@poit，'encoding="utf-8"'，''））
但这是最好的方式吗？
è¿™é‡Œæœ‰å‡ ä»¶äº‹æƒ…éœ€è¦�注æ„�：
The encoding= attribute of the <xml> element is used to denote how the underlying bytes of the XML document are to be interpreted. If the document within the string literal is correct, then there is no need to have the encoding attribute. If there are characters that are incorrect, then the encoding attribute can remain as it will inform the XML conversion on what those characters were originally.
UTF-8 is a Unicode encoding, yet you have the variable and literal as VARCHAR data, not NVARCHAR (which also requires prefixing the string literal with a capital-N). By using VARCHAR and no N-prefix, if there were any characters in the XML document that couldn't fit into the Code Page denoted by the default Collation of whatever database you are in when executing this query, you would have already lost those characters (even if you can see them on screen, they wouldn't be correct in the VARCHAR variable, or if you did a simple SELECT of that literal).
Windows (and .NET, SQL Server, etc) use UTF-16 Little Endian. The Í character, Latin Capital Letter I with Acute, exists in both Code Page 1252 and UTF-16LE as value 205 (e.g. SELECT ASCII('Í'), CHAR(205); ), which is why it works when you remove the encoding="utf-8" and why you didn't "lose" that character by placing it in a VARCHAR literal and variable. HOWEVER, as shown on that linked page, the byte sequence in the UTF-8 encoding is 195, 141 (yes, two bytes). Meaning, that character, if it truly was UTF-8 encoded, would not appear to be that character when placed into a UTF-16LE environment.
The XML conversion looks at that character's byte value of 205 (single byte since it is currently VARCHAR data) and tries to provide the UTF-16LE equivalent of what that sequence is in UTF-8. Except 205 by itself doesn't exist in UTF-8. So you need to add in the next character which is a capital-"A" which has a value of 65. While there are two-byte sequences in UTF-8, none of them are 205, 65. This is why you get the illegal xml character error.
Since the text on screen has to be UTF-16LE, if the source really was UTF-8, then the underlying UTF-8 byte sequence would have to be converted into UTF-16LE. The underlying byte sequence of Í is 195, 141. So we can create that sequence out of regular ASCII characters of Code Page 1252 (since this is, again, currently VARCHAR data) by doing the following:
DECLARE @poit VARCHAR(100);
SET @poit = '<?xml version="1.0" encoding="UTF-8"?><test>V'
+ CHAR(195) + CHAR(141) + 'A</test>';
SELECT CONVERT(XML, @poit);
Returns:
<test>VÍA</test>
Data is still VARCHAR and encoding="utf-8" is still in the <xml> element!
If keeping the data as VARCHAR, then the following change of just the encoding= value works:
DECLARE @poit VARCHAR(100);
SET @poit = '<?xml version="1.0" encoding="Windows-1252"?><test>VÍA</test>';
SELECT CONVERT(XML, @poit);
This assumes that the source encoding really was "Windows-1252", which is Microsoft's version of Latin1_General, which is the basis of the Latin1_General collations.
BUT, there is again no need to even specify the "encoding" if it is the same as the Code Page of the current databases's default collation as that is assumed for any VARCHAR data.
Finally, XML data in SQL Server is UTF-16LE, same as NCHAR and NVARCHAR (and NTEXT, but nobody should be using that anymore).
在处理XML字符串时，请使用NVARCHAR(MAX)作为数据类型（而不是VARCHAR）。
对于没有任何更改字符的字符串（即屏幕上看起来完美无缺的字符串），只需删除encoding="utf-8"即可。无需将其替换为UTF-16，因为这已经被假定为一个NVARCHAR变量或文字（即一个以大写字母N为前缀的字符串）的值所具有的特性。
关于使用VARCHAR(MAX)代替XML甚至NVARCHAR(MAX)以节省空间，请记住，XML数据类型在内部进行了优化，使元素和属性名称仅存储一次，存储在字典中，因此与完全书写出的XML字符串版本相比，它们没有那么多的开销。因此，虽然XML类型将字符串存储为UTF-16LE，但如果 XML文档具有大量重复的元素和/或属性名称，则使用XML类型实际上可能会导致比使用VARCHAR(MAX)更小的占用空间：
DECLARE @ElementBased XML;
SET @ElementBased = (
SELECT * FROM master.sys.all_columns FOR XML PATH('Row')
);
DECLARE @AttributeBased XML;
SET @AttributeBased = (
SELECT * FROM master.sys.all_columns FOR XML RAW('Row')
);
SELECT @ElementBased AS [ElementBasedXML],
@AttributeBased AS [AttributeBasedXML],
DATALENGTH(@ElementBased) AS [ElementBasedXmlBytes],
DATALENGTH(CONVERT(VARCHAR(MAX), @ElementBased)) AS [ElementBasedVarCharBytes],
((DATALENGTH(@ElementBased) * 1.0) / DATALENGTH(CONVERT(VARCHAR(MAX), @ElementBased))
) * 100 AS [XmlElementSizeRelativeToVarcharElementSize],
DATALENGTH(@AttributeBased) AS [AttributeBasedXmlBytes],
DATALENGTH(CONVERT(VARCHAR(MAX), @AttributeBased)) AS [AttributeBasedVarCharBytes],
((DATALENGTH(@AttributeBased) * 1.0) /
DATALENGTH(CONVERT(VARCHAR(MAX), @AttributeBased))) * 100
AS [XmlAttributeSizeRelativeToVarCharAttributeSize];
ElementBasedXmlBytes 1717896
ElementBasedVarCharBytes 5889081
XmlElementSizeRelativeToVarcharElementSize 29.170867237180130482100
AttributeBasedXmlBytes 1544661
AttributeBasedVarCharBytes 3461864
XmlAttributeSizeRelativeToVarCharAttributeSize 44.619343798600984902900
如您所见，对于基于元素的 XML ，XML 数据类型的大小为 VARCHAR(MAX) 版本的 29%，而对于基于属性的 XML ，XML 数据类型的大小为 VARCHAR(MAX) 版本的 44%。
@poit变量的数据类型从VARCHAR(100)更改为NVARCHAR(100)。然后用utf-16替换utf-8编码，这样您的代码会类似于： 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'))
NVARCHAR而不是VARCHAR的观点不错。但是，您需要一直保持这种方式，并在文字前加上大写的“N”，否则可能会丢失数据。另外，也没有必要将utf-8替换为utf-16，因为UTF-16是NVARCHAR数据唯一的编码方式 ;-）。如果您有兴趣，我的回答中有完整的解释 :)。 - Solomon Rutzky
XML、类型和在使用它时实际上比使用VARCHAR(MAX)占用更少空间的内部优化信息。 - Solomon Rutzky