消息9402，级别16，状态1，第9行XML解析：第1行，字符38，无法切换编码 [英] Msg 9402, Level 16, State 1, Line 9 XML parsing: line 1, character 38, unable to switch the encoding

问题描述

从生产到测试环境，一段tsql代码的行为都不相同。当下面的代码在prod上执行时，它会带回数据

A piece of tsql code doesnt behave the same from production to Test environment. When the code below is executed on prod it brings back data

SELECT [col1xml]

FROM [DBName].[dbo].[Table1] (NOLOCK)

WHERE (cast([col1xml] as xml).value('(/Payment/****/trn1)[1]','nvarchar(20)') ='123456'))

但是，相同的代码会在测试中运行时出现以下错误。

However that same code brings back the below error when ran in Test.

Msg 9402，级别16，状态1，第9行
XML解析：第1行字符38，无法切换编码

Msg 9402, Level 16, State 1, Line 9 XML parsing: line 1, character 38, unable to switch the encoding

我已经看到了此UTF转换站点提供的修复程序，并且在两个产品中均有效并测试。见下文。但是我需要向开发人员提供一个答案，说明为什么会发生这种行为，并为他们为什么要更改代码提供理由（如果是这种情况）

I have seen the fix provided by this site of conversion of UTF and this works in both prod and test. See below. However i need to provide an answer to the developers of why this behavior is occurring and a rationale why they should change their code(if that is the case)

WHERE CAST(

REPLACE(CAST(col1xml AS VARCHAR(MAX)), 'encoding="utf-16"', 'encoding="utf-8"')

AS XML).value('(/Payment/****/trn1)[1]','NVARCHAR(max)') ='123456')

我已经比较了两个数据库，并寻找了任何明显的东西，例如ansi null和ansi padding。一切都相同，并且版本相同。这是SQL SERVER 2012 11.0.5388版本。环境之间的数据不同，但表架构相同，col1xml的数据类型为ntext。

I have compared both DB's and looked for anything obvious such as ansi nulls and ansi padding. Everything is the same and the version of SQL Server. This is SQL SERVER 2012 11.0.5388 version. Data between environments is different but the table schema is identical and the data type for col1xml is ntext.

推荐答案

在SQL Server中，您应该将XML存储在键入 XML 的列中。这种本地类型具有很多优点。 快得多，并且具有隐式有效性检查。

In SQL Server you should store XML in a column typed XML. This native type has a got a lot of advantages. It is much faster and has implicit validity checks.

根据我的问题，您将XML存储在 NTEXT 。此类型已弃用已有多个世纪，并且在以后的版本中将不再支持 ！ 您应该尽快更改它！

From your question I take, that you store your XML in NTEXT. This type is deprecated for centuries and will not be supported in future versions! You ought to change this soon!

SQL Server知道两种字符串：

SQL-Server knows two kinds of strings:

• 1个字节的字符串（ CHAR 或 VARCHAR ），它是扩展的ASCII
  
  重要提示：这不是UTF-8！对本机UTF-8的支持将成为后续版本的一部分。


• 2字节字符串（ NCHAR 或 NVARCHAR ），即 UTF-16 （UCS-2）

• 1 byte strings (CHAR or VARCHAR), which is extended ASCII
  Important: This is not UTF-8! Native UTF-8 support will be part of a coming version.
• 2 byte string (NCHAR or NVARCHAR), which is UTF-16 (UCS-2)

如果XML具有带编码的前导声明（在大多数情况下为 utf-8 或 utf-16 ）会遇到麻烦。

If the XML has a leading declaration with an encoding (in most cases this is utf-8 or utf-16) you can get into troubles.

如果XML存储为 2字节字符串（至少 NTEXT 告诉我），声明必须为 utf-16 。使用 1字节字符串时，应为 utf-8 。

If the XML is stored as 2-byte-string (at least the NTEXT tells me this), the declaration has to be utf-16. With a 1-byte-string it should be utf-8.

最好（也是最简单的） ）是完全省略了声明。您不需要它。以适当的类型存储XML将自动终止该声明。

The best (and easiest) was to ommit the declaration completely. You do not need it. Storing the XML in the appropriate type will kill this declaration automatically.

您应该执行的操作：创建一个新列，其类型为 XML ，然后将所有XML洗牌到此列。摆脱任何 TEXT ， NTEXT 和 IMAGE 列

What you should do: Create a new column of type XML and shuffle all your XMLs to this column. Get rid of any TEXT, NTEXT and IMAGE columns you might have!

下一步是：高兴并享受使用本机XML类型的快速便捷：-D

The next step is: Be happy and enjoy the fast and easy going with the native XML type :-D

您输入：环境之间的数据不同

错误在这里发生：

cast([col1xml] as xml)

如果您的列会将XML存储为本机类型，则完全不需要强制转换（非常昂贵！）。但在您的情况下，此转换取决于实际的XML。由于它存储在 NTEXT 中，因此它是 2字节字符串。如果您的XML以声明不支持的编码的声明开头（在大多数情况下 utf-8 ），则此操作将失败。

If your column would store the XML in the native type, you would not need a cast (which is very expensive!!) at all. But in your case this cast depends on the actual XML. As this is stored in NTEXT it is 2-byte-string. If your XML starts with a declaration stating a non-supported encoding (in most cases utf-8), this will fail.

试试这个：

这有效

DECLARE @xml2Byte_UTF16 NVARCHAR(100)='<?xml version="1.0" encoding="utf-16"?><root>test1</root>';
SELECT CAST(@xml2Byte_UTF16 AS XML);

DECLARE @xml1Byte_UTF8 VARCHAR(100)='<?xml version="1.0" encoding="utf-8"?><root>test2</root>';
SELECT CAST(@xml1Byte_UTF8 AS XML);

失败

DECLARE @xml2Byte_UTF8 NVARCHAR(100)='<?xml version="1.0" encoding="utf-8"?><root>test3</root>';
SELECT CAST(@xml2Byte_UTF8 AS XML);

DECLARE @xml1Byte_UTF16 VARCHAR(100)='<?xml version="1.0" encoding="utf-16"?><root>test4</root>';
SELECT CAST(@xml1Byte_UTF16 AS XML);

试玩 VARCHAR 和 NVARCHAR 和 utf-8 和 utf-16 ...

这篇关于消息9402，级别16，状态1，第9行XML解析：第1行，字符38，无法切换编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！