|
OPENXML 通过 XML 文档提供行集视图。 由于 OPENXML 是行集提供程序，因此可在会出现行集提供程序（如表、视图或 OPENROWSET 函数）的 Transact-SQL 语句中使用 OPENXML。
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ]
通过使用 SchemaDeclaration 或指定一个现有 TableName，WITH 子句提供一种行集格式（根据需要还可提供其他映射信息）。 如果没有指定可选的 WITH 子句，则以“边缘”表格式返回结果。 边缘表在单个表中表示 XML 文档的细密结构（例如，元素/属性名、文档层次结构、命名空间、处理说明等）。 下表介绍了“边缘”表的结构。
以下示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示形式。 然后对 XML 文档的内部表示形式执行使用 OPENXML 行集提供程序的 SELECT 语句。 flag 值设置为 1。 该值指示“以属性为中心”的映射。 因此，XML 属性映射到行集中的列。 指定为 /ROOT/Customer 的 rowpattern 标识要处理的 <Customers> 节点。 没有指定可选的 ColPattern（列模式）参数，因为列名与 XML 属性名称匹配。 OPENXML 行集提供程序创建了一个双列行集（CustomerID 和 ContactName），SELECT 语句从该行集中检索必要的列（在本例中检索所有的列）。 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 如果将 flags 设置为 2（表示“以元素为中心”的映射）并执行相同的 SELECT 语句，则由于 XML 文档中没有任何元素名为 CustomerID 或ContactName，所以 XML 文档中针对两个客户的 CustomerID 和 ContactName 的值都返回为 NULL。 Here is the result set. CustomerID ContactName ---------- ----------- NULL NULL NULL NULL 下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等属性。 rowpattern 标识 <OrderDetails> 元素。 ProductID 和Quantity 是 <OrderDetails> 元素的属性。 而 OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的属性。 指定可选的 ColPattern。 这包括以下各项：
尽管“以元素为中心”的映射由 flags 参数指定，但 ColPattern 中指定的映射的优先级高于该映射。 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 以下示例中的示例 XML 文档由 <Customers>、<Orders> 和 <Order_0020_Details> 元素组成。 首先调用 sp_xml_preparedocument 以获得文档句柄。 此文档句柄传递给 OPENXML。 在 OPENXML 语句中，rowpattern (/ROOT/Customers) 标识要处理的 <Customers> 节点。 由于未提供 WITH 子句，因此 OPENXML以“边缘”表格式返回行集。 最后，SELECT 语句检索“边缘”表中的所有列。 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;
本主题中的示例说明如何使用 OPENXML 创建 XML 文档的行集视图。 有关 OPENXML 语法的信息，请参见 OPENXML (Transact-SQL)。 这些示例说明了 OPENXML 的各个方面，但不包括在 OPENXML 中指定元属性。 有关如何在 OPENXML 中指定元属性的详细信息，请参阅在 OPENXML 中指定元属性。
在检索数据时，rowpattern 可用于在确定行的 XML 文档中标识节点。 此外，rowpattern 是用实现 MSXML XPath 所采用的 XPath 模式语言表示的。 例如，如果模式以元素或属性结束，则为 rowpattern 选择的每个元素或属性节点创建一行。 flags 值提供默认映射。 如果 SchemaDeclaration 中没有指定 ColPattern，则假定使用 flags 所指定的映射。 如果在 SchemaDeclaration 中指定了ColPattern，则忽略 flags 值。 指定的 ColPattern 决定了映射是以属性为中心还是以元素为中心，还决定了在处理溢出数据和未用完数据时的行为。 A.使用 OPENXML 执行简单的 SELECT 语句
此示例中的 XML 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 OPENXML 语句说明了以下信息：
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 由于 < @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
结果如下： CustomerID ContactName ---------- -------------------- VINET Paul Henriot LILAS Carlos Gonzlez 请注意，sp_xml_preparedocument 返回的文档句柄在批处理持续时间（而非会话持续时间）内有效。 B.为行集列与 XML 属性及元素之间的映射指定 ColPattern
此示例说明如何在可选的 ColPattern 参数中指定 XPath 模式，以提供行集列和 XML 属性以及元素之间的映射。 此示例中的 XML 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 OPENXML 语句说明了以下信息：
为了举例说明，将 flags 参数值设置为 2，表示以元素为中心的映射。 但是，ColPattern 中指定的映射覆盖了此映射。 即 ColPattern 中指定的 XPath 模式将行集中的列映射到属性。 这将产生以属性为中心的映射。 在 WITH 子句内的 SchemaDeclaration 中，也可以用 ColName 和 ColType 参数指定 ColPattern。 可选的 ColPattern 是指定的 XPath 模式，表示以下内容：
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 文档 < 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。 在 < D.指定 text() XPath 函数作为 ColPattern
此示例中的 XML 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 OPENXML 语句说明了以下信息：
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 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 OPENXML 语句说明了以下信息：
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 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 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
结果作为边缘表返回。 您可以对边缘表编写查询以获得信息。 例如：
G.指定以属性结束的 rowpattern
此示例中的 XML 文档由 < 首先调用 sp_xml_preparedocument 存储过程以获得文档句柄。 此文档句柄传递给 OPENXML。 OPENXML 语句说明了以下信息：
在 WITH 子句的 SchemaDeclaration 中，还可以用 ColName 和 ColType 参数指定 ColPattern。 可选的 ColPattern 是指定的 XPath 模式，用以表示以下内容：
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 文档中有多个文本节点，则包含 ColPattern (text()) 的 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>
请注意结果中的以下内容：
J.从多值属性中检索单值
XML 文档会含有多值属性。 例如，IDREFS 属性可以是多值属性。 在 XML 文档内，多值属性值被指定为一个字符串，并用空格分隔值。 在以下 XML 文档中，<Student> 元素的 attends 属性与 <Class> 的 attendedBy 属性都是多值属性。 从多值 XML 属性中检索单值并将每个值存储到数据库中的不同行中，这要求额外的工作。 下例显示了此过程。 此示例 XML 文档由以下元素组成：
<Student> 中的 attends 属性和 <Class> 中的 attendedBy 属性表示 Student 表与 Class 表之间的一种 m:n 关系。 一个学生可在很多班上课，而一个班也可有很多学生。 假设希望拆分此文档，并将它保存到下列数据库中：
以下是具体步骤： -- 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 编码数据中检索二进制数据
二进制数据经常包括在使用 base64 编码的 XML 中。 当使用 OPENXML 拆分此 XML 时，将接收到 base64 编码数据。 该示例演示如何将 base64 编码数据转换回二进制。
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
结果如下。 返回的二进制数据是表 T 的原始二进制数据。 Col1 BinaryCol ----------- --------------------- 1 0x1234567890 |
|
|
