OPENXML (Transact-SQL)用 SQL語法抓取 XML格式檔案

參考來源

讀取資料來源

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
<Child>SON</Child>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.


顯示SQL讀取 XML

方式一

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20),
Child varchar(10))


以下為結果集：
CustomerID ContactName Child
---------- -------------------- ------------------
VINET Paul Henriot NULL
LILAS Carlos Gonzlez NULL


如果相同的 SELECT 陳述式以 flags 設為 2 來執行 (表示元素中心對應)，由於 元素不具有任何子元素，因此 XML 文件中兩個客戶的 CustomerID 及 ContactName 值會以 NULL 傳回。

※ 在文章中沒有寫到子元素的部分，紅色部分為子元素的結果，補充子元素的部分

以下為結果集：

CustomerID ContactName Child
---------- ----------- ------------------
NULL NULL SON
NULL NULL NULL

因此如果要子元素同顯示，則要改用可指定名稱


方式二

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity',
Child varchar(10) '/ROOT/Customer/Child'
)


以下為結果集：
OrderID CustomerID OrderDate ProdID Qty Child
------------------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12 SON
10248 VINET 1996-07-04 00:00:00.000 42 10 SON
10283 LILAS 1996-08-16 00:00:00.000 72 3 SON


方式三：擷取邊緣資料表中的所有資料行。

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc