OPENXML 通过 XML 文档提供行集视图。由于 OPENXML 是行集提供程序,因此可在会出现行集提供程序(如表、视图或 OPENROWSET 函数)的 Transact-SQL 语句中使用 OPENXML。
通过使用 SchemaDeclaration 或指定一个现有 TableName,WITH 子句提供一种行集格式(根据需要还可提供其他映射信息)。如果没有指定可选的 WITH 子句,则以“边缘”表格式返回结果。边缘表在单个表中表示 XML 文档的细密结构(例如,元素/属性名、文档层次结构、命名空间、处理说明等)。
下表介绍了“边缘”表的结构。
列名 |
数据类型 |
说明 |
---|---|---|
id |
bigint |
文档节点的唯一 ID。 根元素的 ID 值为 0。保留负 ID 值。 |
parentid |
bigint |
标识节点的父节点。此 ID 所标识的父节点不一定是父元素,而是取决于此 ID 所标识节点的子节点的 NodeType。例如,如果节点是文本节点,则其父节点可能是属性节点。 如果节点位于 XML 文档的顶层,则其 ParentID 为 NULL。 |
nodetype |
int |
标识节点类型。一个对应于 XML DOM 节点类型编号的整数。 节点类型包括: 1 = 元素节点 2 = 属性节点 3 = 文本节点 |
localname |
nvarchar |
给出元素或属性的本地名称。如果 DOM 对象没有名称,则为 NULL。 |
prefix |
nvarchar |
节点名称的命名空间前缀。 |
namespaceuri |
nvarchar |
节点的命名空间 URI。如果值为 NULL,则命名空间不存在。 |
datatype |
nvarchar |
元素或属性行的实际数据类型,否则为 NULL。数据类型是从内联 DTD 中或从内联架构中推断得出。 |
prev |
bigint |
前一个同级元素的 XML ID。如果前面没有同级元素,则为 NULL。 |
text |
ntext |
包含文本格式的属性值或元素内容(如果“边缘”表项不需要值,则为 NULL)。 |
A. 使用带 OPENXML 的简单 SELECT 语句
以下示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示形式。然后对 XML 文档的内部表示形式执行使用 OPENXML 行集提供程序的 SELECT 语句。
flag 值设置为 1。该值指示“以属性为中心”的映射。因此,XML 属性映射到行集中的列。指定为 /ROOT/Customer 的 rowpattern 标识要处理的 <Customers> 节点。
没有指定可选的 ColPattern(列模式)参数,因为列名与 XML 属性名称匹配。
OPENXML 行集提供程序创建了一个双列行集(CustomerID 和 ContactName),SELECT 语句从该行集中检索必要的列(在本例中检索所有的列)。
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> </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))
下面是结果集:
如果将 flags 设置为 2(表示“以元素为中心”的映射)并执行相同的 SELECT 语句,则由于 <Customers> 元素没有任何子元素,所以针对 XML 文档中两个客户的 CustomerID 和 ContactName 的值都返回为 NULL。
下面是结果集:
B. 为列和 XML 属性之间的映射指定 ColPattern
下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等属性。rowpattern 标识 <OrderDetails> 元素。ProductID 和 Quantity 是 <OrderDetails> 元素的属性。而 OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的属性。
指定可选的 ColPattern。这包括以下各项:
-
行集中的 OrderID、CustomerID 和 OrderDate 映射到 XML 文档中的 rowpattern 所标识节点的父节点属性。
-
行集中的 ProdID 列映射到 ProductID 属性,行集中的 Qty 列映射到 rowpattern 中所标识节点的 Quantity 属性。
尽管“以元素为中心”的映射由 flags 参数指定,但 ColPattern 中指定的映射的优先级高于该映射。
DECLARE @idoc int DECLARE @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"> <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')
下面是结果集:
C. 获得边缘表格式的结果
以下示例中的示例 XML 文档由 <Customers>、<Orders> 和 <Order_0020_Details> 元素组成。首先调用 sp_xml_preparedocument 以获得文档句柄。此文档句柄传递给 OPENXML。
在 OPENXML 语句中,rowpattern (/ROOT/Customers) 标识要处理的 <Customers> 节点。由于未提供 WITH 子句,因此 OPENXML 以“边缘”表格式返回行集。
最后,SELECT 语句检索“边缘”表中的所有列。
DECLARE @idoc int DECLARE @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