[SQL] 將 Table 資料匯出成 XML @ 黃昏的甘蔗

201101141306[SQL] 將 Table 資料匯出成 XML

從SQL2005之後便對XML提供了完整的支援，也有許多專家強調XML是現在與未來重要的基礎，那我們當然也不能自立於外，也要來瞭解一下SQL在XML部份，提供了什麼樣的能力，以及該怎麼將資料表的內容匯出轉成XML格式...

這一篇為記錄性質文章，目的是將不同的 XML 輸出語法，對應將得到什麼樣的結果

Q：為何要用XML（eXtensible Markup Language）？
A：我們可以透過XML的標記方式，將資料以共通的方式來表示，以達不同平台、不同目的之間訊息的交換。
XML was designed to transport and store data.

W3C 給的說明（What is XML? ）

XML stands for EXtensible Markup Language
XML is a markup language much like HTML
XML was designed to carry data, not to display data
XML tags are not predefined. You must define your own tags
XML is designed to be self-descriptive
XML is a W3C Recommendation

本篇用的例子 - Use Northwind 資料庫

底下顯示不同定義，所得到不同的XML輸出結果（框的顏色不同，只是為了區隔，方便閱讀之用）

⊕ FOR XML RAW ⊕
以 row 為 Element 名稱描述每一筆資料，裡面的欄位以 Attribute 描述，Attribute 的值即為欄位內容的值

請注意 FOR XML RAW 的字是 RAW ，裡面描述的字是 ROW ～不一樣的唷

SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock]
FROM [Northwind].[dbo].[Products]
WHERE ProductID <3
FOR XML RAW

輸出結果：

點開之後，會出現XML格式的檔案

◎FOR XML RAW, ELEMENTS◎
以 ROW 為 ELEMENT 名稱描述每一筆資料，裡面的欄位以ELEMENT描述，欄位的名稱等於ELEMENT的名稱，ELEMENT的值等於欄位的值。

請注意 ELEMENTS 記得要加上 S 唷

SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock]
FROM [Northwind].[dbo].[Products]
WHERE ProductID <3
FOR XML RAW, ELEMENTS

輸出結果：

<row>
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<UnitPrice>18.0000</UnitPrice>
<UnitsInStock>39</UnitsInStock>
</row>
<row>
<ProductID>2</ProductID>
<ProductName>Chang</ProductName>
<UnitPrice>19.0000</UnitPrice>
<UnitsInStock>17</UnitsInStock>
</row>

⊕增加根標籤（ROOT Tag）與改變預設的名稱⊕

SELECT [ProductID],[ProductName],[UnitPrice],[UnitsInStock]
FROM [Northwind].[dbo].[Products]
WHERE ProductID <3
FOR XML RAW('列'), ROOT('根')

輸出結果：
<根>
<列 ProductID="1" ProductName="Chai" UnitPrice="18.0000" UnitsInStock="39" />
<列 ProductID="2" ProductName="Chang" UnitPrice="19.0000" UnitsInStock="17" />
</根>

SELECT [ProductID]'產品代號',[ProductName]'產品名稱'
,[UnitPrice]'價格',[UnitsInStock]'庫存量'
FROM [Northwind].[dbo].[Products]
WHERE ProductID <3
FOR XML RAW('列'), ROOT('產品文件'), ELEMENTS

輸出結果：
<產品文件>
<列>
    <產品代號>1</產品代號>
    <產品名稱>Chai</產品名稱>
    <價格>18.0000</價格>
    <庫存量>39</庫存量>
</列>
<列>
    <產品代號>2</產品代號>
    <產品名稱>Chang</產品名稱>
    <價格>19.0000</價格>
    <庫存量>17</庫存量>
</列>
</產品文件>

◎FOR XML AUTO◎
會自動以 Table 名稱當作 ROW ELEMENT 的名稱

FOR XML AUTO

輸出結果：
<Products ProductID="1" ProductName="Chai" UnitPrice="18.0000" UnitsInStock="39" />
<Products ProductID="2" ProductName="Chang" UnitPrice="19.0000" UnitsInStock="17" />

⊕FOR XML PATH⊕ 路徑節點
直接在別名 Alias 上定義這個欄位是位於文件路徑上哪一層，並且是採用 element （預設）或是 attribute （加＠表示）

SELECT [ProductID]'@PID',[ProductName]'PName'
,[UnitPrice]'PName/Price',[UnitsInStock]'PName/Stock'
FROM [Products] WHERE ProductID <3
FOR XML PATH('列'),ROOT('Product_Detail')

輸出結果：
<Product_Detail>
<列 PID="1">
<PName>Chai<Price>18.0000</Price><Stock>39</Stock></PName>
</列>
<列 PID="2">
<PName>Chang<Price>19.0000</Price><Stock>17</Stock></PName>
</列>
</Product_Detail>

SELECT [ProductID]'@PID',[ProductName] 'PName/@PName'
,[UnitPrice]'PName/Price',[UnitsInStock]'Pname/Stock'
FROM [Products] WHERE ProductID <3
FOR XML PATH('列'),ROOT('Product_Detail')

輸出結果：
<Product_Detail>
<列 PID="1">
    <PName PName="Chai">
      <Price>18.0000</Price>
      <Stock>39</Stock>
    </PName>
</列>
<列 PID="2">
    <PName PName="Chang">
      <Price>19.0000</Price>
      <Stock>17</Stock>
    </PName>
</列>
</Product_Detail>

◎除錯◎ 可以看得出來下面的 T-SQL 跟上面那一個有哪裡不一樣嗎？怎麼會得到差異這麼大的結果

是的～就是 N 與 n 的不同，XML是區分大小寫的，一旦大小寫不同，他就會認為是不同一個 Tag，所以他將 Stock 另列在 Pname 的階層下

SELECT [ProductID]'@PID',[ProductName] 'PName'
,[UnitPrice]'PName/Price',[UnitsInStock]'Pname/Stock'
FROM [Products] WHERE ProductID <3
FOR XML PATH('列'),ROOT('Product_Detail')

輸出結果：
<Product_Detail>
<列 PID="1">
    <PName>Chai<Price>18.0000</Price></PName>
    <Pname>
      <Stock>39</Stock>
    </Pname>
</列>
<列 PID="2">
    <PName>Chang<Price>19.0000</Price></PName>
    <Pname>
      <Stock>17</Stock>
    </Pname>
</列>
</Product_Detail>

*** Null 內容的欄位，在轉匯到 XML 之後，會自動被移除 ***

我們將原始資料稍微修改一下，把兩個欄位改成NULL，來觀察一下在 Attribute 與 element 的結果

欄位NULL的都消失了，這樣誰會知道到底他有匯出幾個欄位阿～

Attribute 輸出結果：
<row ProductID="1" ProductName="Chai" UnitPrice="18.0000" />
<row ProductID="2" ProductName="Chang" UnitsInStock="17" />

Element 輸出結果：
<row>
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<UnitPrice>18.0000</UnitPrice>
</row>
<row>
<ProductID>2</ProductID>
<ProductName>Chang</ProductName>
<UnitsInStock>17</UnitsInStock>
</row>

◎XSINIL◎ 處理 NULL 欄位

FOR XML RAW, ELEMENTS XSINIL
輸出結果：
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ProductID>1</ProductID>
<ProductName>Chai</ProductName>
<UnitPrice>18.0000</UnitPrice>
<UnitsInStock xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ProductID>2</ProductID>
<ProductName>Chang</ProductName>
<UnitPrice xsi:nil="true" />
<UnitsInStock>17</UnitsInStock>
</row>

FOR XML RAW, XSINIL
FOR XML RAW XSINIL
輸出結果：

XSINIL 只能用在 Element 無法用在 Attribute

訊息102，層級15，狀態1，行6 接近'XSINIL' 之處的語法不正確。
訊息102，層級15，狀態1，行6 接近'RAW' 之處的語法不正確。