由于“UTF-8”属性，将 VARCHAR(MAX) 转换为 XML 时出错

您存储在 varchar(max) 列中的 XML 应如下所示。

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

应该用ó双字节值表示ó。

如果您的列中没有存储 UTF-8 编码的字符串，正确的方法是在将值转换为 XML 数据类型之前从 XML 中删除编码。

我认为你有一个更深层次的问题。UTF-8 允许比 SQL Server 中的常规非 Unicode 排序规则更多的字符。因此，为了安全起见，您应该使用具有 UTF-8 排序规则的 SQL Server 2019（如果由于多种原因这不可行/不可取），操作系统使用（尝试）nvarchar 而不是 varchar。

如果您害怕从 varchar 到 nvarchar 的存储增加，您可以使用行压缩。但这需要 SQL Server 2016 之前的企业版。

这里发生的是：

1. 该XML类型在内部将数据存储为 UTF-16 Little Endian（至少在大多数情况下）。源编码是什么并不重要，最终结果将是 UTF-16 LE（并且没有<xml>标签，因此没有encoding="..."）。
2. 将字符串转换为XML：
   1. 转换的是字符串的字节，而不是字符（稍后将解释差异）
   2. NVARCHAR数据假定为 UTF-16 LE。如果有<xml>标签并且它包含该encoding属性，则唯一有效的值为"UTF-16".
   3. VARCHAR当没有<xml>标签时，或者如果标签存在但没有encoding属性，则假定数据位于与数据的整理相关联的 8 位代码页中。否则，数据将被解释为在encoding属性中指定的代码页中编码（即使它是在与数据整理相关的代码页中编码的）。
3. 您的数据很可能被编码为 Windows 代码页 1252（这取决于数据所在列的排序规则，而不是实例甚至数据库的排序规则，但由于您提到实例正在使用Latin1_General_BIN，所以它是安全的- 足以假设该列正在使用相同的排序规则）。
4. ó代码页 Windows-1252 中字符的代码点是： 0xF3。
5. 但是，该<xml>标记声明 XML 数据被编码为 UTF-8。
6. 在 UTF-8 中，0xF3 必须后跟三个字节，每个字节介于0x80和0xBF之间，但在您的数据中，它后面是 a p，其值为0x70。因此，您会收到“非法 xml 字符”错误（因为encoding="UTF-8"告诉转换函数字节是有效的 UTF-8 字节；转换看不到ó字符）。

您的选择是：

1. 理想情况下，列将被转换为XML 并且标签的encoding属性<xml>或整个<xml>标签本身将在进入的过程中被删除。并且，如果在创建一个重复的元素和/或属性名称时，XML数据类型可以节省空间内部名称的字典（查找列表）并使用 ID 值记录结构。

2. 将[data]列设置为使用 UTF-8 排序规则（SQL Server 2019 中的新功能，因此不适合您）

3. 将[data]列设置为NVARCHAR，并删除标签的encoding属性<xml>，或整个<xml>标签。

4. 将传入的字符串转换为 UTF-8 字节。所以这个ó字符是 UTF-8 中的两个字节：0xC3B3，ó在 Windows-1252 中出现。

   DECLARE @Good VARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8"?><a>hell'
           + CONVERT(VARCHAR(MAX), 0xC3B3)
           + '</a>';
   SELECT @Good, CONVERT(XML, @Good)
   -- <?xml version="1.0" encoding="UTF-8"?><a>helló</a>
   --
   -- <a>helló</a>
   

笔记：

• 简单地删除标签的encoding属性<xml>，或整个<xml>标签，不是一种选择。当然，它会在这种特殊情况下工作，但它不会在所有情况下工作，因为 SQL Server 2014 中没有可用的列VARCHAR 和UTF-8 排序规则。因此，Windows 代码页 1252 中不可用的任何 Unicode 字符将转换为?或??（取决于 BMP 字符或补充字符）：

  DECLARE @Test VARCHAR(MAX) = '<test>ó - ☢ - ?</test>';
  SELECT @Test, CONVERT(XML, @Test);
  -- <test>ó - ? - ??</test>
  --
  -- <test>ó - ? - ??</test>
  

• 不要简单地将列的排序规则更改为不同的语言环境/文化。虽然这可能会消除错误，但它只能通过默默地消除导致错误的数据来实现。例如：

  DECLARE @Data NVARCHAR(MAX) = N'ó';
  SELECT CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) AS [Latin1_General],
      CONVERT(VARCHAR(MAX), @Data COLLATE Latin1_General_BIN) COLLATE
               Cyrillic_General_CI_AS AS [Cyrillic];
  /*
  Latin1_General    Cyrillic
  ó                 o
  */
  

  “Cyrillic”使用与“Latin1_General”不同的代码页，并且该ó字符在 Cyrillic 代码页上不可用。但是，有一个“最佳拟合”映射，这就是我们最终得到 ao而不是 a 的原因?。
• 您和任何使用 SQL Server 2008 或更高版本的人，确实应该使用_100_级别排序规则。此外，任何使用 SQL Server 2012 或更高版本的人都应该使用以（用于补充字符）_100_结尾的级别排序规则。_SC最后，当在 SQL Server 2005 或更高版本上需要二进制排序规则时，请使用一个结尾_BIN2（请参阅我的帖子了解原因）。
• 此问题与查询是临时查询还是在存储过程（T-SQL 或 SQLCLR）中无关。

为您的 varchar 使用兼容的排序规则

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max) COLLATE Latin1_General_100_CI_AI_SC_UTF8
);

INSERT INTO t VALUES
(1, N'<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>
')
GO

SELECT
  id, 
  CAST(data as XML)
FROM
  t;
GO

编号 | （无列名）                                                                                                            
-: | :------------------------------------------------ -------------------------------------------------- ----------------------
 1 | <PARAM><TAB DIM="30" ID="ZC3D2_1" SIZE="5"><LIN NUM="1"><FLD NAME="ZDOC" TYPE="Char">费兰洛佩斯</FLD></ LIN></TAB></PARAM>

db<>在这里摆弄