SQL 2005 TIPS - Convert NText To Xml Data Type
3 | 12,969 |
使用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
搞定收工!
Comments
# by singular
最後完成的SQL指令是不是有少一個")" 右括弧 ?
# by Jeffrey
to singular, 的確漏了,謝謝指正!
# by chicken
這類鐵板我也踢過好幾次 :~ 最後結論是,除非你能精確控制儲存 XML 的 storage 用的編碼 (比如自己存檔案),否則千萬別自己加上這段 PI 宣告.. 碰過其它是 HTML FORM POST 過來的 XML,明明只放字串,也是硬被加上編碼的宣告,最後抓半天才抓到問題 -_-