MS SQL 轉換欄位XML資料變成Table

Edward Kuo

Follow

Jul 23, 2020 · 4 min read

這個需求實在讓我頓時搞得很頭大，不知道怎樣時空背景關係，會在資料庫裏面把多個筆資料與多個欄位利用XML方式組合成一筆放入在資料欄位中，所以，就下面的案例來說，XML所代表是這兩筆資料並包含可用的三個欄位，

<Maximum>
 <row MEMMAX=”32.0000" MEMMAXUNIT=”GB” MEMMAX_DESC=””/><row MEMMAX=”64.0000" MEMMAXUNIT=”GB” MEMMAX_DESC=””/>
</Maximum>

但因為這樣並無法直接對內容進行篩選且要做其他用法也相對不方便，唯有把這些XML轉換成DataSet才可以進行處理

因此要怎樣把XML轉換呢?我們可以透過OUTER APPLY 針對這個欄位XML節點做處理。不過有一點特別不一樣就是如果儲存XML值的欄位格式本身並非是XML而是Varchar的話，還必須轉型把欄位換成XML型態

CONVERT(XML, '欄位名稱') AS 欄位

不然就不能進行以下語法，下面與法是代表這個資料表中，共有三個欄位是儲存XML的資料。分別是

• ColumnA

<A>
 <row A1=”32.0000" A2=”GB” /><row A1=”64.0000" A2=”GB” />
</A>

• ColumnB

<B>
 <row B1=”32.0000" B2=”GB” /><row B1=”64.0000" B2=”GB” />
</B>

• ColumnC

<C>
 <row C1=”32.0000" C2=”GB” /><row C1=”64.0000" C2=”GB” />
</C>

要把上面三個XML資料轉換成Table，可以參考下面語法

SELECT DeviceID,DeviceName,
   ColumnA.s.value(‘@A1’, ‘varchar(max)’) AS A1,
   ColumnA.s.value(‘@A2’, ‘varchar(max)’) AS A2,
   ColumnB.m.value(‘@B1’, ‘varchar(max)’) AS B1,
   ColumnC.d.value(‘@C1’, ‘varchar(max)’) AS C1,
   ColumnC.d.value(‘@C2’, ‘varchar(max)’) AS C2
FROM
(
 SELECT DeviceID,DeviceLine,
        CONVERT(XML, 'Column A') AS ColumnA,
        CONVERT(XML, 'Column B') AS ColumnB,
        CONVERT(XML, 'Column C') AS ColumnC,
 FROM [dbo].[TableName]
) AS TB
OUTER APPLY TB.ColumnA.nodes(‘A/row’) AS ColumnA(s)
OUTER APPLY TB.ColumnB.nodes(‘B/row’) AS ColumnB(m)
OUTER APPLY TB.ColumnC.nodes(‘C/row’) AS ColumnC(d)

透過Outer Apply方式決定要從哪個節點開始取出XML的elements，而在 ColumnA.s.value(‘@A1’, ‘varchar(max)’) AS A1 其中就取出elements是A1後面所帶的值。

因此，透過這樣方式就可以去拆解XML，變成我們要的資料表，當然這樣另一個影響就是原本儲存是一筆的資料就可以能變成多筆了，但也會讓資料更好處理