|
OPENXML 通過(guò) XML 文檔提供行集視圖。 由于 OPENXML 是行集提供程序，因此可在會(huì)出現(xiàn)行集提供程序（如表、視圖或 OPENROWSET 函數(shù)）的 Transact-SQL 語(yǔ)句中使用 OPENXML。
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ]
通過(guò)使用 SchemaDeclaration 或指定一個(gè)現(xiàn)有 TableName，WITH 子句提供一種行集格式（根據(jù)需要還可提供其他映射信息）。 如果沒(méi)有指定可選的 WITH 子句，則以“邊緣”表格式返回結(jié)果。 邊緣表在單個(gè)表中表示 XML 文檔的細(xì)密結(jié)構(gòu)（例如，元素/屬性名、文檔層次結(jié)構(gòu)、命名空間、處理說(shuō)明等）。 下表介紹了“邊緣”表的結(jié)構(gòu)。
sp_xml_preparedocument." xml:space="preserve">以下示例使用 sp_xml_preparedocument 創(chuàng)建 XML 圖像的內(nèi)部表示形式。 SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document." xml:space="preserve">然后對(duì) XML 文檔的內(nèi)部表示形式執(zhí)行使用 OPENXML 行集提供程序的 SELECT 語(yǔ)句。 1." xml:space="preserve">flag 值設(shè)置為 1。 該值指示“以屬性為中心”的映射。 因此，XML 屬性映射到行集中的列。 /ROOT/Customer identifies the <Customers> nodes to be processed." xml:space="preserve">指定為 /ROOT/Customer 的 rowpattern 標(biāo)識(shí)要處理的 <Customers> 節(jié)點(diǎn)。 沒(méi)有指定可選的 ColPattern（列模式）參數(shù)，因?yàn)榱忻c XML 屬性名稱匹配。 OPENXML rowset provider creates a two-column rowset (CustomerID and ContactName) from which the SELECT statement retrieves the necessary columns (in this case, all the columns). " xml:space="preserve">OPENXML 行集提供程序創(chuàng)建了一個(gè)雙列行集（CustomerID 和 ContactName），SELECT 語(yǔ)句從該行集中檢索必要的列（在本例中檢索所有的列）。 DECLARE @idoc int, @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>
</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.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
Here is the result set. CustomerID ContactName ---------- -------------------- VINET Paul Henriot LILAS Carlos Gonzlez SELECT statement is executed with flags set to 2, indicating element-centric mapping, the values of CustomerID and ContactName for both of the customers in the XML document are returned as NULL, because there are not any elements named CustomerID or ContactName in the XML document." xml:space="preserve">如果將 flags 設(shè)置為 2（表示“以元素為中心”的映射）并執(zhí)行相同的 SELECT 語(yǔ)句，則由于 XML 文檔中沒(méi)有任何元素名為 CustomerID 或ContactName，所以 XML 文檔中針對(duì)兩個(gè)客戶的 CustomerID 和 ContactName 的值都返回為 NULL。 Here is the result set. CustomerID ContactName ---------- ----------- NULL NULL NULL NULL 下面的查詢從 XML 文檔返回客戶 ID、訂單日期、產(chǎn)品 ID 和數(shù)量等屬性。 <OrderDetails> elements." xml:space="preserve"> rowpattern 標(biāo)識(shí) <OrderDetails> 元素。 ProductID and Quantity are the attributes of the <OrderDetails> element." xml:space="preserve"> ProductID 和Quantity 是 <OrderDetails> 元素的屬性。 OrderID, CustomerID, and OrderDate are the attributes of the parent element (<Orders>)." xml:space="preserve">而 OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的屬性。 指定可選的 ColPattern。 這包括以下各項(xiàng)：
盡管“以元素為中心”的映射由 flags 參數(shù)指定，但 ColPattern 中指定的映射的優(yōu)先級(jí)高于該映射。 DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<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">v
<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 @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
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');
Here is the result set. 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 <Customers>, <Orders>, and <Order_0020_Details> elements." xml:space="preserve">以下示例中的示例 XML 文檔由 <Customers>、<Orders> 和 <Order_0020_Details> 元素組成。 首先調(diào)用 sp_xml_preparedocument 以獲得文檔句柄。 OPENXML." xml:space="preserve">此文檔句柄傳遞給 OPENXML。 OPENXML statement, the rowpattern (/ROOT/Customers) identifies the <Customers> nodes to process." xml:space="preserve">在 OPENXML 語(yǔ)句中，rowpattern (/ROOT/Customers) 標(biāo)識(shí)要處理的 <Customers> 節(jié)點(diǎn)。 OPENXML returns the rowset in an edge table format." xml:space="preserve">由于未提供 WITH 子句，因此 OPENXML以“邊緣”表格式返回行集。 SELECT statement retrieves all the columns in the edge table." xml:space="preserve">最后，SELECT 語(yǔ)句檢索“邊緣”表中的所有列。 DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;
本主題中的示例說(shuō)明如何使用 OPENXML 創(chuàng)建 XML 文檔的行集視圖。 有關(guān) OPENXML 語(yǔ)法的信息，請(qǐng)參見(jiàn) OPENXML (Transact-SQL)。 這些示例說(shuō)明了 OPENXML 的各個(gè)方面，但不包括在 OPENXML 中指定元屬性。 有關(guān)如何在 OPENXML 中指定元屬性的詳細(xì)信息，請(qǐng)參閱在 OPENXML 中指定元屬性。
在檢索數(shù)據(jù)時(shí)，rowpattern 可用于在確定行的 XML 文檔中標(biāo)識(shí)節(jié)點(diǎn)。 此外，rowpattern 是用實(shí)現(xiàn) MSXML XPath 所采用的 XPath 模式語(yǔ)言表示的。 例如，如果模式以元素或?qū)傩越Y(jié)束，則為 rowpattern 選擇的每個(gè)元素或?qū)傩怨?jié)點(diǎn)創(chuàng)建一行。 flags 值提供默認(rèn)映射。 如果 SchemaDeclaration 中沒(méi)有指定 ColPattern，則假定使用 flags 所指定的映射。 如果在 SchemaDeclaration 中指定了ColPattern，則忽略 flags 值。 指定的 ColPattern 決定了映射是以屬性為中心還是以元素為中心，還決定了在處理溢出數(shù)據(jù)和未用完數(shù)據(jù)時(shí)的行為。 A.使用 OPENXML 執(zhí)行簡(jiǎn)單的 SELECT 語(yǔ)句
此示例中的 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
SELECT 語(yǔ)句隨后將檢索 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
結(jié)果如下： CustomerID ContactName ---------- -------------------- VINET Paul Henriot LILAS Carlos Gonzlez 由于 < @xmlDocument 也可以是 xml 類型或 (n)varchar(max) 類型。 如果 XML 文檔中的 < 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
結(jié)果如下： CustomerID ContactName ---------- -------------------- VINET Paul Henriot LILAS Carlos Gonzlez 請(qǐng)注意，sp_xml_preparedocument 返回的文檔句柄在批處理持續(xù)時(shí)間（而非會(huì)話持續(xù)時(shí)間）內(nèi)有效。 B.為行集列與 XML 屬性及元素之間的映射指定 ColPattern
此示例說(shuō)明如何在可選的 ColPattern 參數(shù)中指定 XPath 模式，以提供行集列和 XML 屬性以及元素之間的映射。 此示例中的 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
為了舉例說(shuō)明，將 flags 參數(shù)值設(shè)置為 2，表示以元素為中心的映射。 但是，ColPattern 中指定的映射覆蓋了此映射。 即 ColPattern 中指定的 XPath 模式將行集中的列映射到屬性。 這將產(chǎn)生以屬性為中心的映射。 在 WITH 子句內(nèi)的 SchemaDeclaration 中，也可以用 ColName 和 ColType 參數(shù)指定 ColPattern。 可選的 ColPattern 是指定的 XPath 模式，表示以下內(nèi)容：
SELECT 語(yǔ)句隨后將檢索 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
結(jié)果如下： 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 也可以對(duì)已指定為 ColPattern 的 XPath 模式重新指定，以將 XML 元素映射到行集列。 這將產(chǎn)生以元素為中心的映射。 在下例中，XML 文檔 < 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 參數(shù)設(shè)置為 3，表示將應(yīng)用以屬性為中心的映射和以元素為中心的映射。 在這種情況下，首先應(yīng)用以屬性為中心的映射，然后對(duì)所有未處理的列應(yīng)用以元素為中心的映射。 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
下面是查詢結(jié)果。 CustomerID ContactName ---------- -------------------- VINET Paul Henriot LILAS Carlos Gonzlez 以屬性為中心的映射應(yīng)用于 CustomerID。 在 < D.指定 text() XPath 函數(shù)作為 ColPattern
此示例中的 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
SELECT 語(yǔ)句隨后將檢索 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
結(jié)果如下： 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。 當(dāng)表具有想要的結(jié)構(gòu)而不具備列模式（ColPattern 參數(shù)）時(shí)，這非常有用。 此示例中的 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
SELECT 語(yǔ)句隨后將檢索 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
結(jié)果如下： 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.獲得邊緣表格式的結(jié)果
在下例中，在 OPENXML 語(yǔ)句中未指定 WITH 子句。 因此，OPENXML 所生成的行集具有邊緣表格式。 SELECT 語(yǔ)句將返回邊緣表中的所有列。 示例中的示例 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
繼而，SELECT 語(yǔ)句將檢索邊緣表中的所有列。 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
結(jié)果作為邊緣表返回。 您可以對(duì)邊緣表編寫(xiě)查詢以獲得信息。 例如：
G.指定以屬性結(jié)束的 rowpattern
此示例中的 XML 文檔由 < 首先調(diào)用 sp_xml_preparedocument 存儲(chǔ)過(guò)程以獲得文檔句柄。 此文檔句柄傳遞給 OPENXML。 OPENXML 語(yǔ)句說(shuō)明了以下信息：
在 WITH 子句的 SchemaDeclaration 中，還可以用 ColName 和 ColType 參數(shù)指定 ColPattern。 可選的 ColPattern 是指定的 XPath 模式，用以表示以下內(nèi)容：
SELECT 語(yǔ)句隨后將檢索 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
結(jié)果如下： ProdID Qty OID ----------- ----------- ------- 11 12 10248 42 10 10248 72 3 10283 H.指定具有多個(gè)文本節(jié)點(diǎn)的 XML 文檔
如果在 XML 文檔中有多個(gè)文本節(jié)點(diǎn)，則包含 ColPattern (text()) 的 SELECT 語(yǔ)句只返回第一個(gè)文本節(jié)點(diǎn)，而不是返回所有節(jié)點(diǎn)。 例如： 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 語(yǔ)句返回 T 作為結(jié)果，而不是返回 TaU。 I.在 WITH 子句中指定 xml 數(shù)據(jù)類型
在 WITH 子句中，映射到 xml 數(shù)據(jù)類型列的列模式（不論是類型化的還是非類型化的）必須返回一個(gè)空序列，或者元素、處理指令、文本節(jié)點(diǎn)和注釋的序列。 數(shù)據(jù)將轉(zhuǎn)換為 xml 數(shù)據(jù)類型。 在下例中，WITH 子句中的表架構(gòu)聲明包括 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() 函數(shù)。 結(jié)果如下： 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>
請(qǐng)注意結(jié)果中的以下內(nèi)容：
J.從多值屬性中檢索單值
XML 文檔會(huì)含有多值屬性。 例如，IDREFS 屬性可以是多值屬性。 在 XML 文檔內(nèi)，多值屬性值被指定為一個(gè)字符串，并用空格分隔值。 在以下 XML 文檔中，<Student> 元素的 attends 屬性與 <Class> 的 attendedBy 屬性都是多值屬性。 從多值 XML 屬性中檢索單值并將每個(gè)值存儲(chǔ)到數(shù)據(jù)庫(kù)中的不同行中，這要求額外的工作。 下例顯示了此過(guò)程。 此示例 XML 文檔由以下元素組成：
<Student> 中的 attends 屬性和 <Class> 中的 attendedBy 屬性表示 Student 表與 Class 表之間的一種 m:n 關(guān)系。 一個(gè)學(xué)生可在很多班上課，而一個(gè)班也可有很多學(xué)生。 假設(shè)希望拆分此文檔，并將它保存到下列數(shù)據(jù)庫(kù)中：
以下是具體步驟： -- 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
K.從 XML 中的 base64 編碼數(shù)據(jù)中檢索二進(jìn)制數(shù)據(jù)
二進(jìn)制數(shù)據(jù)經(jīng)常包括在使用 base64 編碼的 XML 中。 當(dāng)使用 OPENXML 拆分此 XML 時(shí)，將接收到 base64 編碼數(shù)據(jù)。 該示例演示如何將 base64 編碼數(shù)據(jù)轉(zhuǎn)換回二進(jìn)制。
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 .value 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,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h ;
GO
結(jié)果如下。 返回的二進(jìn)制數(shù)據(jù)是表 T 的原始二進(jìn)制數(shù)據(jù)。 Col1 BinaryCol ----------- --------------------- 1 0x1234567890 |
|
|
