SQL2005中讲ntext类型转换为XML类型

以下摘自

http://blog.darkthread.net/blogs/darkthreadtw/archive/2008/09/18/sql2005-nvarchar-to-xml.aspx

 

XmlString是一個NText欄位，裡面放存放的內容來自*.xml的讀取內容，試著將XmlStringCol轉成XML會發生錯誤:

SELECT TOP 1 CONVERT(XML, XmlString) FROM myTable

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

經過一番測試，總算明白問題出在哪了。從*.xml中讀出的內容，第一列放的是XML宣告<?xml version="1.0" encoding="UTF-8"?>，而記得嗎? SQL中一向是用UCS-2儲存資料的，這形成一個矛盾: 明明Encoding是UCS-2，但XML內容卻又宣稱自己是UTF-8，導致了轉換失敗。

我想到的解決方法是去掉encoding屬性，讓SQL自行看著辦，但由於欄位是NText，必須要轉成NVarChar(MAX)才可動用Replace狸貓換太子，於是指令要改成:

SELECT TOP 1 CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), XmlString), 'encoding="UTF-8"', '')) FROM myTable