[Develop]OpenXML

2011-03-18

Lab1、OpenXML

這個Lab我們要探討SQL所提供的一個預存程序 OpenXML ! 這個章節可以對應到 Biztalk Lab15的[ Biztalk
& SQL Server Store Procedure ]。在以 Web Service 實

踐SOA的架構中，所傳遞的文件為 XML格式。當這份文件要被儲存到資料庫時，並需按照資料庫的欄位結構儲存而不是整份XML存在一個欄位的時候

，SQL Server 提供了一個很好用的預存程序 [ OpenXML]。讓程式設計師可以用 Select 元素的方式來儲存
XML 文件元素內容到資料庫欄位之中，大大降低

以 .net 撰寫應用程式來儲存XML文件所要做的工。

本範例中我們要將下列的XML存進SQL 資料庫，我們假設自己是統一公司，我們接收一份訂單，訂單中整合了各家廠商的訂單。如下圖：我們會接到

家樂湖、大潤花、全佳的訂單。接著我們要將這張訂單分由兩個資料庫儲存。分別是存放訂購商家資訊的 "主檔資料庫"
與 "定購明細" 的 Detail 資料庫

。※ 這個範例中我們不詳細追究資料庫中正規劃的問題，主要是研究 OpenXML 預存程序的使用方式。

建立SQL資料庫與資料表。

在SQL Server中建立一個資料庫 [ OpenXML ] 與兩個資料表，分別為 [ Master] 與 [ Detail ]。資料庫你也可以隨你的喜好命名。這邊我貼上建立

兩個資料表的指令。在 [ Master ]資料表中我們建立兩個欄位，[CustomerID] 、[CustomerName]，分別存放訂單中的顧客編號與顧客名稱。而在
[ Detail ]

資料表中我們建立三個欄位 [ProductID] 、 [ProductName] 、 [Count] 、[CustomerID]。分別用來存放
產品的編號、產品的名稱、訂購數量以及這個訂購品

像是那個顧客訂的，所以紀錄顧客編號( 這裡也是指向Order資料表的外鍵，原本指向訂單Master資料表的外鍵用訂單編號比較適當，但是這邊就暫時

不複雜化這個範例 )。

Master 資料表 SQL 建立指令

CREATE TABLE [dbo].[Master](
[CustomerID] [char](8) NULL,
[CustomerName] [nvarchar](80) NULL
) ON [PRIMARY]
GO

Detail 資料表 SQL 建立指令

CREATE TABLE [dbo].[Detail](
[ProductID] [char](4) NULL,
[ProductName] [nvarchar](50) NULL,
[Count] [int] NULL,
[CustomerID] [char](8) NULL
) ON [PRIMARY]
GO

撰寫SQL指令。

接著開啟 MicrSoft SQL Server 的 Query Analyzer。這是SQL 2000 的一個 Client 連接到資料庫的工具，若你有SQL
2000 的安裝光碟，可以安裝上

這個工具。浩呆本身很喜歡用這個工具，它可以連接到 SQL 2000、SQL 2005。本身所吃的資源也相對的比較少，浩呆個人覺得還滿好用的。

我們在SQL Query Analyzer 撰寫SQL程式。首先我們宣告一個 int 變數 @docCursor 和一個 nvarchar
變數 @xmlDoc。

@xmlDoc 變數是用來存放整份XML文件，而@docCursor是將存放XML文件在記憶體中的文件位址指標指向。執行 [ sp_xml_preparedocument
]預存程序

時給其這兩個變數，他會將文件的記憶體位址指標回傳給@docCursor這個變數，因此我們可以運用這個變數來取得文件。

接著我們看到 [ INSERT Master SELECT * FROM OPENXML(@docCursor, N'/Orders/Master')
WITH Master ]，借由OPENXML預存程序來對記憶體中XML文件

作搜尋，我們可以很輕易的將文件的特定元素取出；並且經由 Insert DataBaseName 的語法儲存到資料庫中。記住當你儲存完畢後，記得將記憶體中的

XML文件給釋放掉喔。

觀察結果！

接著我們 Select 兩個資料庫來觀察文件儲存的情況，您可以發現資料被依據[ 主檔 ] 跟 [ 明細檔案 ] 被分門別類的儲存起來了。這樣的方式是不是比寫

.Net 程式來得快上許多。若將這個動作再包裝成一個 [ 預存程序 ] 就可以更簡單的被利用。

回首頁