T-SQL OPENXML

OPENXML

OPENXML 通过 XML 文档提供行集视图。由于OPENXML 是行集提供程序,因此可在会出现行集提供程序(如表、视图或 OPENROWSET 函数)的 Transact-SQL 语句中使用 OPENXML。

语法

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]

参数

idoc

是 XML 文档的内部表式法的文档句柄。通过调用 sp_xml_preparedocument 创建 XML 文档的内部表式法。

rowpattern

是 XPath 模式,用来标识要作为行处理的节点(这些节点在 XML 文档中,该文档的句柄由 idoc 参数传递)。

flags

表示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。flag 为可选输入参数,可以是下列值之一。

字节值
描述
0默认为以特性为中心的映射。
1使用以特性为中心的映射。
在某些情况下,可以将它与 XML_ELEMENTS 组合使用。使用时首先应用以特性为中心的映射,然后对于所有仍未处理的列应用以元素为中心的映射。
2使用以元素为中心的映射。
在某些情况下,可以将它与 XML_ATTRIBUTES 组合使用。使用时先应用以特性为中心的映射,然后对于所有仍未处理的列应用以元素为中心的映射。
8可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用(逻辑 OR)。
在检索的上下文中,该标志指明不应将已消耗的数据复制到溢出属性 @mp:xmltext

SchemaDeclaration

是窗体的架构定义:
ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]

ColName
是行集中的列名。
ColType
是行集中列的 SQL 数据类型。如果列类型不同于特性的基础 XML 数据类型,那么将发生类型压制。如果列的类型是 TIMESTAMP,则当从 OPENXML 行集中进行选择时,将忽略 XML 文档中现有的值并返回自动填充值。
ColPattern
是可选的通用 XPath 模式,它说明应如何将 XML 节点映射到列。如果没有指定 ColPattern,那么发生默认的映射(由 flags 指定的以特性为中心或以元素为中心的映射)。

指定为 ColPattern 的 XPath 模式用于指定特殊的映射性质(如果发生以特性为中心和以元素为中心的映射),这些特殊的映射性质可以重写或增强由标志所指定的默认映射。

指定为 ColPattern 的通用 XPath 模式也支持元属性。

MetaProperty
是由 OPENXML 提供的元属性之一。如果指定元属性,则该列包含元属性提供的信息。这些元属性使您得以析取 XML 节点的信息(如相对位置、命名空间信息),以提供比文本化表示法更详细的信息。

TableName

如果具有期望架构的表已经存在且不要求列模式,则为给定的表名(而不是 SchemaDeclaration)。

通过使用 SchemaDeclaration 或者指定一个现有的 TableNameWITH 子句提供一种行集格式(根据需要还提供其它映射信息)。如果没有指定可选的 WITH 子句,那么以 edge table 格式返回结果。边缘表在单个表中表示 XML 文档的细密结构(例如,元素/特性名、文档层次结构、命名空间、PI 等)。

下表描述边缘表的结构。

列名数据类型描述
idbigint是文档节点的唯一 ID。

根元素的 ID 值为 0。保留负 ID 值。

parentidbigint标识节点的父节点。此 ID 所标识的父节点不一定是父元素,而是取决于此 ID 所标识节点的子节点的 NodeType。例如,如果节点是文本节点,则其父节点可能是特性节点。

如果节点位于 XML 文档的顶层,则其 ParentID 为 NULL。

nodetypeint标识节点类型。是对应于 XML DOM 节点类型编号的整数(有关节点信息,请参见 DOM)。

三种节点类型是:

1 = 元素节点
2 = 特性节点
3 = 文本节点

localnamenvarchar给出元素或特性的本地名称。如果 DOM 对象没有名称则为 NULL。
prefixnvarchar是节点名称的命名空间前缀。
namespaceurinvarchar是节点的命名空间 URI。如果值是 NULL,则命名空间不存在。
datatypenvarchar是元素或特性行的实际数据类型,否则是 NULL。从内嵌 DTD 中或从内嵌架构中推断数据类型。
prevbigint是前一个兄弟元素的 XML ID。如果前面没有兄弟元素则为 NULL。
textntext包含文本格式的特性值或元素内容(如果边缘表项不需要值则为 NULL)。

示例
A.使用带有 OPENXML 的 SELECT 语句。

下面的示例使用 sp_xml_preparedocument 创建 XML 图像的内部表示。然后对 XML 文档的内部表示法执行使用 OPENXML 行集提供程序的 SELECT 语句。

flag 值设置为 1,表示以特性为中心的映射。因此,XML 特性映射到行集中的列。指定为 /ROOT/Customersrowpattern 标识要处理的 <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))

下面是结果集:

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez

如果将 flags 设置为 2(表示以元素为中心的映射)并执行相同的 SELECT 语句,由于 <Customers> 元素没有任何子元素,则对于 XML 文档中两个 Customer 的 CustomerIDContactName 的值都作为 NULL 返回。

下面是结果集:

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL
B. 为列和 XML 特性之间的映射指定 ColPattern

下面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等特性。rowpattern 标识 <OrderDetail> 元素。ProductIDQuantity 是 <OrderDetails> 元素的特性。而 CustomerIDOrderDate 是父元素 (<Orders>) 的特性。

指定可选的 ColPattern,表示:

  • 行集中的 OrderIDCustomerIDOrderDate 列映射到 XML 文档中 rowpattern 所标识节点的父节点的特性。

  • 行集中的 ProdID 列映射到 ProductID 特性,而行集中的 Qty 列映射到 rowpattern 所标识节点的 Quantity 特性。

尽管以元素为中心的映射由 flag 参数指定,但 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')

结果如下:

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
C. 获得边缘表格式的结果

在下例中,在 OPENXML 语句中未指定 WITH 子句。因此,OPENXML 所生成的行集具有边缘表格式。SELECT 语句返回边缘表中的所有列。

下例中的示例 XML 文档由 <Customer>、<Order> 和 <Order_0020_Details> 元素组成。

首先调用 sp_xml_preparedocument 以获得文档句柄。此文档句柄传递到 OPENXML。

在 OPENXML 语句中

  • rowpattern (/ROOT/Customer) 标识要处理的 <Customer> 节点。

  • 未提供 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 using OPENXML rowset provider
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc

结果作为边缘表返回。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值