-
SQL 2005 TIPS - Convert NText To Xml Data Type
-
使用SSMS SELECT XmlStringCol FROM myTable，發現由於SSRS的結果字元數限制(65535個字元)，在結果視窗複製欄位內容再到Notepad貼上，XML的後半截不見了。
雖然這個限制可以修改(如下圖)，但一時想要展現高超的T-SQL指令技巧，我決定把它轉成XML，如此可以閃過長度限制，還可以直接在SSMS中檢視XML內容，一舉兩得。
噹!! (痛)
我踢到鐵板了!
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
搞定收工!