SQL讀取XML欄位，sqlxml欄位

SQL讀取XML欄位，sqlxml欄位

最近要對一套老系統進行資料移轉，這樣苦逼的工作就來了，需要新舊兩套系統資料表欄位進行對比，最終將老系統的資料移轉到新系統中來。

儘管資料結構不一致、部分欄位不一致，也只是噁心一點麻煩一點，勉勉強強速度慢點，還是可以整理出來，並編寫好遷移的SQL語句。

一步一步走了好幾步了，出來個更噁心的怪物了！強大的XML資料欄位

本來應該是單獨存到一個子表的資訊，在老系統中硬生生的存到了一個XML欄位中

<root>    <people id=1 name=tom/>    <people id=2 name=jim/></root>

在資料移轉的時候，必須要把這些資料，轉移到一個子表中，那麼我需要可以對XML進行讀取，如下：

IDNAME1TOM1JIM

開動大腦想想，怎麼破呢？

分兩步走吧，第一步，首選實現針對單獨一條資料的XML資訊實現讀取，然後再實現多條資料的XML讀取，當然能讀出來，也就可以再插入了，O(∩_∩)O哈哈~

1.實現單獨一條資料XML的讀取

DECLARE @XML XMLSELECT @XML='<root>    <people id="1" name="tom"/>    <people id="2" name="jim"/></root>'SELECT v.value('@id[1]','VARCHAR(20)') AS ID, v.value('@name[1]','VARCHAR(20)') AS NameFROM @XML.nodes('/root/people') x(v)

2.實現多條資料XML的讀取

由於每一條XML資料都可能有多條資料，在這裡，我使用了遊標，每次遍曆一條資料，讀取XML，然後針對該條資料的自資料進行資料插入更新

DECLARE @ID VARCHAR(36)DECLARE @XML XMLDECLARE CUR CURSOR FORSELECT id FROM testTableFOR READ ONLYOPEN CUR;FETCH NEXT FROM CUR INTO @ID    WHILE @@FETCH_STATUS=0BEGIN SELECT @XML=xmlField FROM testTable WHERE id=@IDSELECT v.value('@id[1]','VARCHAR(20)') AS ID,v.value('@name[1]','VARCHAR(20)') AS NameFROM @XML.nodes('/root/people') x(v)--根據該SQL修改為插入或者更新語句的SQL，可以將其括起來，作為一個虛表FETCH NEXT FROM CUR INTO @IDENDCLOSE CURDEALLOCATE CUR

OK，問題到此搞定！

大家有什麼好的辦法，可以溝通交流。（第二步驟，我是一次性執行資料移轉使用，如果是常規經常性使用，那麼是很不建議使用遊標的！就需要一個更好的解決方案。。。

原文參考：http://www.cnblogs.com/l1pe1/archive/2010/07/28/1787254.html

裡邊好多各種讀取、操作XML的方式，我這裡只使用了很小的一部分，大家想學習更新，看原文收穫也會很大的！