使用 OPENXML
在此主題下的範例將說明如何使用 OPENXML 來建立 XML 文件的資料列集檢視。如需 OPENXML 語法的詳細資訊，請參閱 OPENXML (Transact-SQL)。範例中將說明 OPENXML 的各個方面，但是不指定 OPENXML 的中繼屬性。如需如何指定 OPENXML 的中繼屬性的詳細資訊，請參閱＜在 OPENXML 中指定中繼屬性＞。
範例
在擷取資料時，rowpattern 是用來識別 XML 文件中決定資料列的節點。另外，rowpattern 是以 XPath 模式語言表示，該語言使用於 MSXML XPath 實作中。比方說，如果模式結尾是元素或屬性，則會為 rowpattern 所選取的每一個元素或屬性節點建立一個資料列。
flags 值提供預設對應。若在 SchemaDeclaration 中未指定 ColPattern，則假設為 flags 中所指定的對應。若在 SchemaDeclaration 中指定 ColPattern，則忽略 flags 值。指定的 ColPattern 將決定處理溢位和未消耗資料的對應 (屬性中心或元素中心) 以及行為。
A. 以 OPENXML 執行簡單的 SELECT 陳述式
此範例中的 XML 文件是由 <Customer>、<Order> 和 <OrderDetail> 元素組成。OPENXML 陳述式所擷取的客戶資訊是來自於 XML 文件中的兩資料行資料列集：CustomerID 和 ContactName。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
rowpattern (/ROOT/Customer) 識別要處理的 <Customer> 節點。
flags 參數值設為 1，表示屬性中心的對應。因此，XML 屬性對應至 SchemaDeclaration 中定義之資料列集的資料行。
在 WITH 子句內的 SchemaDeclaration 中，指定的 ColName 值與對應的 XML 屬性名稱相符。因此不會在 SchemaDeclaration 中指定 ColPattern 參數。
然後，SELECT 陳述式擷取由 OPENXML 所提供之資料列集內的所有資料行。
DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @DocHandle
以下是結果：
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
由於 <Customer> 元素不具有任何子元素，若相同的 SELECT 陳述式以 flags 設為 2 來執行 (表示元素中心的對應)，則兩個客戶的 CustomerID 及 ContactName 值將以 NULL 傳回。
@xmlDocument 也可以是 xml 類型或 (n)varchar(max) 類型。
如果 XML 文件中的 <CustomerID> 和 <ContactName> 是子元素，元素中心的對應會擷取這些值。
DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>LILAS</CustomerID>
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @XmlDocumentHandle
以下是結果：
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
請注意，sp_xml_preparedocument 傳回的文件控制代碼是對批次的期間有效，而不是對工作階段有效。
B. 指定 ColPattern 來進行資料列集資料行與 XML 屬性和元素之間的對應
此範例將說明如何在選擇性的 ColPattern 參數中指定 XPath 模式，以提供資料列集資料行與 XML 屬性和元素之間的對應。
此範例中的 XML 文件是由 <Customer>、<Order> 和 <OrderDetail> 元素組成。OPENXML 陳述式所擷取的客戶及訂單資訊是來自於 XML 文件中的資料列集 (CustomerID、OrderDate、ProdID 及 Qty)。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
- rowpattern (/ROOT/Customer/Order/OrderDetail) 識別要處理的 <OrderDetail> 節點。
舉例來說，flags 參數值設為 2，表示元素中心的對應。不過，ColPattern 中指定的對應會覆寫此對應。也就是說，ColPattern 中所指定的 XPath 模式會將資料列集的資料行對應到屬性。這會導致屬性中心的對應。
在 WITH 子句內的 SchemaDeclaration 中，亦使用 ColName 與 ColType 參數來指定 ColPattern。選擇性 ColPattern 是指定的 XPath 模式，它表示下列各項：
資料列集的 OrderID、CustomerID 和 OrderDate 資料行對應到 rowpattern 所識別節點的父系之屬性，且 rowpattern 識別 <OrderDetail> 節點。因此，CustomerID 和 OrderDate 資料行對應到 <Order> 元素的 CustomerID 和 OrderDate 屬性。
資料列集的 ProdID 及 Qty 資料行對應至 rowpattern 中所識別節點的 ProductID 及 Quantity 屬性。
然後，SELECT 陳述式擷取由 OPENXML 所提供之資料列集內的所有資料行。
DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
EXEC sp_xml_removedocument @XmlDocumentHandle
以下是結果：
OrderID CustomerID OrderDate ProdID Qty
-------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
指定為 ColPattern 的 XPath 模式也可以指定為將 XML 元素對應到資料列集資料行。這會導致元素中心的對應。在下列範例中，XML 文件 <CustomerID> 和 <OrderDate> 是 <Orders> 元素的子元素。由於 ColPattern 覆寫了 flags 參數中所指定的對應，因此在 OPENXML 中並未指定 flags 參數。
DECLARE @docHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
EXEC sp_xml_removedocument @docHandle
C. 結合以屬性中心的對應及元素中心的對應
在此範例中，flags 參數設為 3，表示將套用屬性中心和元素中心的兩種對應。在此情況下，會先套用屬性中心的對應，然後所有尚未處理的資料行則套用元素中心的對應。
DECLARE @docHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" >
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @docHandle
以下是結果：
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
屬性中心的對應套用於 CustomerID。<Customer> 元素中沒有 ContactName 屬性。因此，套用元素中心的對應。
D. 指定 text() XPath 函數為 ColPattern
此範例中的 XML 文件是由 <Customer> 和 <Order> 元素組成。OPENXML 陳述式擷取的資料列集是由 <Order> 元素的 oid 屬性、rowpattern 所識別的節點父系的識別碼以及元素內容的分葉值字串所組成。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
rowpattern ((/root/Customer/Order) 識別要處理的 <Order> 節點。
flags 參數值設為 1，表示屬性中心的對應。因此，XML 屬性對應至 SchemaDeclaration 中所定義的資料列集資料行。
在 WITH 子句內的 SchemaDeclaration 中，資料列集資料行名稱 oid 和 amount 符合相對應的 XML 屬性名稱。因此未指定 ColPattern 參數。針對資料列集中的 comment 資料行， XPath 函數 text() 指定為 ColPattern。這將會覆寫 flags 中所指定之屬性中心的對應，而資料行將包含元素內容的分葉值字串。
然後，SELECT 陳述式擷取由 OPENXML 所提供之資料列集內的所有資料行。
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(1000)
--sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @docHandle
以下是結果：
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
E. 在 WITH 子句中指定 TableName
此範例在 WITH 子句中指定 TableName 而不是 SchemaDeclaration。若您具有所需結構的資料表，且不需要資料行模式 (ColPattern 參數)，這會是相當有用的方式。
此範例中的 XML 文件是由 <Customer> 和 <Order> 元素組成。OPENXML 陳述式所擷取的訂單資訊是來自於 XML 文件中的三資料行資料列集 (oid、date 及 amount)。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
rowpattern ((/root/Customer/Order) 識別要處理的 <Order> 節點。
WITH 子句中沒有 SchemaDeclaration。相反地，有指定資料表名稱。因此，資料表結構描述是做為資料列集結構描述使用。
flags 參數值設為 1，表示屬性中心的對應。因此，由 rowpattern 識別的元素屬性是對應到相同名稱的資料列集資料行。
然後，SELECT 陳述式擷取由 OPENXML 所提供之資料列集內的所有資料行。
-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(1000)
-- Sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
satisfied</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH T1
EXEC sp_xml_removedocument @docHandle
以下是結果：
oid date amount
----- --------------------------- ----------
O1 1996-01-20 00:00:00.000 3.5
O2 1997-04-30 00:00:00.000 13.4
O3 1999-07-14 00:00:00.000 100.0
O4 1996-01-20 00:00:00.000 10000.0
F. 取得邊緣資料表格式的結果
在此範例中，OPENXML 陳述式未指定 WITH 子句。因此，OPENXML 所產生的資料列集具有邊緣資料表格式。SELECT 陳述式以邊緣資料表傳回所有資料行。
此範例中的範例 XML 文件是由 <Customer>、<Order> 和 <OrderDetail> 元素組成。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
rowpattern (/ROOT/Customer) 識別要處理的 <Customer> 節點。
未提供 WITH 子句。因此，OPENXML 以邊緣資料表格式傳回資料列集。
然後，SELECT 陳述式擷取邊緣資料表中的所有資料行。
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(1000)
SET @xmlDocument = N'<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>
</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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer')
EXEC sp_xml_removedocument @docHandle
結果是以邊緣資料表傳回。您可以針對邊緣資料表寫入查詢以取得資訊。例如：
以下查詢傳回文件中 Customer 節點的數目。由於未指定 WITH 子句，因此 OPENXML 傳回邊緣資料表。SELECT 陳述式查詢邊緣資料表。
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer'下列查詢傳回元素類型之 XML 節點的本機名稱。
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname
G. 指定 rowpattern 結束於屬性
此範例中的 XML 文件是由 <Customer>、<Order> 和 <OrderDetail> 元素組成。OPENXML 陳述式所擷取的訂單詳細資訊是來自於 XML 文件中的三資料行資料列集 (ProductID、Quantity 和 OrderID)。
首先，呼叫 sp_xml_preparedocument 預存程序以取得文件控制代碼。接著將此文件控制代碼傳遞至 OPENXML。
OPENXML 陳述式說明下列各項：
rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) 結束於 XML 屬性 (ProductID)。在結果資料列集中，為每個在 XML 文件中選取的屬性節點建立資料列。
在此範例中，未指定 flags 參數。相反地，由 ColPattern 參數指定對應。
在 WITH 子句內的 SchemaDeclaration 中，亦使用 ColName 和 ColType 參數來指定 ColPattern。選擇性 ColPattern 是指定的 XPath 模式，它表示下列各項：
針對資料列集內的 ProdID 資料行指定為 ColPattern 的 XPath 模式 (.) 識別內容節點，即目前節點。根據所指定的 rowpattern，這是 <OrderDetail> 元素的 ProductID 屬性。
針對資料列集內的 Qty 資料行所指定的 ColPattern**../@Quantity**，識別內容節點 <ProductID> 之父節點 <OrderDetail> 的 Quantity 屬性。
同樣地，針對資料列集內的 OID 資料行所指定的 ColPattern**../../@OrderID**，識別內容節點的父節點之父系 <Order> 的 OrderID 屬性。父節點是 <OrderDetail>，內容節點是 <ProductID>。
然後，SELECT 陳述式擷取由 OPENXML 所提供之資料列集內的所有資料行。
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(1000)
--Sample XML document
SET @xmlDocument =N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
WITH ( ProdID int '.',
Qty int '../@Quantity',
OID int '../../@OrderID')
EXEC sp_xml_removedocument @docHandle
以下是結果：
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. 指定含有多個文字節點的 XML 文件
若在 XML 文件中具有多個文字節點，含有 ColPatterntext() 的 SELECT 陳述式將只傳回第一個文字節點，而不是所有節點。例如：
DECLARE @h int
EXEC sp_xml_preparedocument @h OUTPUT,
N'<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />'
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()')
EXEC sp_xml_removedocument @h
SELECT 陳述式傳回的結果為 T，而非 TaU。
I. 在 WITH 子句中指定 xml 資料類型
在 WITH 子句中，對應到 xml 資料類型資料行的資料行模式，不論具類型或不具類型，都必須傳回空序列或元素的序列、處理指示、文字節點和註解。此資料轉換成 xml 資料類型。
在下列範例中，WITH 子句中的資料表結構描述宣告包含 xml 類型資料行。
DECLARE @h int
DECLARE @x xml
set @x = '<Root>
<row id="1"><lname>Duffy</lname>
<Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address>
</row>
<row id="2"><lname>Wang</lname>
<Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address>
</row>
</Root>'
EXEC sp_xml_preparedocument @h output, @x
SELECT *
FROM OPENXML (@h, '/Root/row', 10)
WITH (id int '@id',
lname varchar(30),
xmlname xml 'lname',
OverFlow xml '@mp:xmltext')
EXEC sp_xml_removedocument @h
特別是，您要將 xml 類型變數 (@x) 傳遞至 sp_xml_preparedocument() 函數。
以下是結果：
id lname xmlname OverFlow
--- ------- ------------------------------ -------------------------------
1 Duffy <lname>Duffy</lname> <row><Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address></row>
2 Wang <lname>Wang</lname> <row><Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address></row>
請注意結果的下列各項：
若為 varchar(30) 類型的 lname 資料行，會從對應的 <lname> 元素擷取其值。
若為 xml 類型的 xmlname 資料行，會傳回相同名稱元素做為它的值。
此旗標設為 10。10 表示 2 + 8，其中 2 指示元素中心的對應，8 表示只有未耗用的 XML 資料才加入至 WITH 子句中定義的 OverFlow 資料行。如果您將旗標設為 2，則整個 XML 文件會複製到在 WITH 子句中指定的 OverFlow 資料行。
假如 WITH 子句中的資料行是具類型的 XML 資料行，但 XML 執行個體不符合此結構描述，則會傳回錯誤。
I. 從多重值屬性中擷取個別的值
XML 文件可以擁有多重值的屬性。例如，IDREFS 屬性可為多重值。在 XML 文件中，多重值的屬性值是指定為字串，並以空格區隔其值。在下列 XML 文件中，<Student> 元素的 attends 屬性與 <Class> 的 attendedBy 屬性為多重值。從多重值 XML 屬性中擷取個別的值，並將每個值儲存於資料庫中的個別資料列需要額外的工作。此範例顯示其處理過程。
此範例 XML 文件由下列元素構成：
<Student>
id (學生識別碼)、name 與 attends 屬性。attends 屬性為多重值屬性。
<Class>
id (班級識別碼)、name 與 attendedBy 屬性。attendedBy 屬性為多重值屬性。
<Student> 中的 attends 屬性與 <Class> 中的 attendedBy 屬性代表 Student 與 Class 資料表之間的 m:n 關聯性。一位學生可以選擇多種學科而一種學科可以收授多位學生。
假設您要切割此文件並將文件儲存於資料庫，如下所示：
將 <Student> 資料儲存於 Students 資料表中。
將 <Class> 資料儲存於 Courses 資料表中。
將 Student 與 Class 之間的 m:n 關聯性資料儲存於 CourseAttendence 資料表中。取出這些值需要額外的工作。若要擷取此資訊並將之儲存於資料表，請使用下列預存程序：
Insert_Idrefs_Values
將學科識別碼與學生識別碼的值插入 CourseAttendence 資料表。
Extract_idrefs_values
從每個 <Course> 元素中擷取個別的學生識別碼。使用邊緣資料表來擷取這些值。
以下為其步驟：
-- Create these tables:
DROP TABLE CourseAttendance
DROP TABLE Students
DROP TABLE Courses
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
)
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
)
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
)
go
-- Create these stored procedures:
DROP PROCEDURE f_idrefs
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int
DECLARE @att varchar(5)
SET @sp = 0
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ')
SET @att = LEFT(@t, @sp-1)
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')')
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp)
END
Go
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500)
DECLARE @id varchar(5)
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath)
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END
CLOSE fillidrefs_cursor
DEALLOCATE fillidrefs_cursor
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>'
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance'
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM CourseAttendance
EXECUTE sp_xml_removedocument @h
J. 從 XML 的 Base64 編碼資料中擷取二進位資料
二進位資料常常使用 Base64 編碼而包含在 XML 中。當您使用 OPENXML 來切割此 XML 時，會接收到 Base64 編碼資料。此範例顯示如何撰寫 CLR 函數將 Base64 編碼資料轉換回到二進位。
遵循這些步驟來建立範例 CLR 函數：
下列 C# 程式碼將 Base64 編碼資料轉換成二進位。將程式碼以 base64.cs 的檔名儲存至 c:\temp 資料夾。
using System; public class Sample { public static byte[] ConvertBase64ToBinary(string base64String) { if (base64String == null) { return null; } return Convert.FromBase64String(base64String); } }編譯程式碼及建立程式庫 base64.dll。
csc /target:library base64.cs使用 CREATE ASSEMBLY (Transact-SQL)，建立受管理的應用程式模組。您必須將 "your_computer" 取代成您的電腦名稱。
drop assembly asbly_base64 go create assembly asbly_base64 from '\\your_computer\c$\temp\base64.dll' go如果您沒有啟用 CLR 支援，可執行下列陳述式來啟用 CLR：
sp_configure 'clr enabled', 1 reconfigure with override go建立 CLR 函數，以 Base64 編碼資料做為輸入，並傳回二進位資料。作法是在受管理應用程式模組中呼叫此函數。
create function fn_getBinaryFromBase64String( @s nvarchar(max) ) returns varbinary(max) as external name asbly_base64.Sample.ConvertBase64ToBinary go
您現在可以執行下列動作來測試 CLR 函數：
以範例二進位資料建立資料表。
使用 FOR XML 查詢和 BINARY BASE64 選項來建構將二進位資料編碼成 base64 的 XML。
使用 OPENXML 來切割 XML。OPENXML 傳回的資料將為 Base64 編碼資料。接下來，呼叫 CLR 函數將它轉換回二進位。
CREATE TABLE T (Col1 int primary key, Col2 varbinary(100))
go
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890)
go
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64
go
-- result
-- <T Col1="1" Col2="EjRWeJA="/>
-- Now shredd the sample XML using OPENXML.
-- Call the fn_ getBinaryFromBase64String function to convert
-- the base64 encoded data returned by OPENXML to binary.
declare @h int
exec sp_xml_preparedocument @h output, '<T Col1="1" Col2="EjRWeJA="/>'
SELECT Col1,
dbo.fn_getBinaryFromBase64String(Col2) as BinaryCol
FROM openxml(@h, '/T')
with (Col1 integer, Col2 nvarchar(max))
exec sp_xml_removedocument @h
go
以下是結果。傳回的二進位資料是資料表 T 的原始二進位資料。
Col1 BinaryCol
----------- ---------------------
1 0x1234567890