由於“UTF-8”屬性，將 VARCHAR(MAX) 轉換為 XML 時出錯

我需要使用與此類似的模式來探勘日誌表：

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

Columndata以這種格式儲存從 Web 服務接收到的 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>

當我嘗試將此文本轉換為 XML 時，出現下一個錯誤：

XML解析：第xx行，字元48，非法xml字元

可以通過刪除<xml>標籤或至少刪除encoding屬性來解決。

注意：如果沒有特殊字元ó，即使我不刪除<xml>標籤，它也可以正常工作。

問題

有沒有辦法在不替換或刪除<xml>標籤的情況下將其轉換為 XML？

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>在這裡擺弄

更新

伺服器排序規則為：Latin1_General_BIN

但即使我嘗試將排序規則更改為我通常的伺服器排序規則，它也不起作用。

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

這裡發生的是：

1. 該XML類型在內部將數據儲存為 UTF-16 Little Endian（至少在大多數情況下）。源編碼是什麼並不重要，最終結果將是 UTF-16 LE（並且沒有<xml>標籤，因此沒有encoding="..."）。

2. 將字元串轉換為XML：

3. 轉換的是字元串的字節，**而不是字元（稍後將解釋差異）

4. NVARCHAR數據假定為 UTF-16 LE。如果有<xml>標籤並且它包含該encoding屬性，則唯一有效的值為"UTF-16".

5. VARCHAR當沒有<xml>標籤時，或者如果標籤存在但沒有encoding屬性，則假定數據位於與數據的整理相關聯的 8 位程式碼頁中。否則，數據將被解釋為在encoding屬性中指定的程式碼頁中編碼（即使它是在與數據整理相關的程式碼頁中編碼的）。

6. 您的數據很可能被編碼為 Windows 程式碼頁 1252（這取決於數據所在列的排序規則，而不是實例甚至數據庫的排序規則，但由於您提到實例正在使用Latin1_General_BIN，所以它是安全的- 足以假設該列正在使用相同的排序規則）。

7. ó程式碼頁 Windows-1252 中字元的程式碼點是： 0xF3。

8. 但是，該<xml>標記聲明 XML 數據被編碼為 UTF-8。

9. 在 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>標籤，不是一種選擇。VARCHAR 當然，它會在這種特殊情況下工作，但由於列存在且UTF-8 排序規則在 SQL Server 2014 中不可用，它不會在所有情況下工作。因此，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）中無關。

引用自：https://dba.stackexchange.com/questions/278684