关于SqlServer中openxml的使用 导入XML

最新推荐文章于 2023-03-12 12:05:59 发布
最新推荐文章于 2023-03-12 12:05:59 发布
阅读量1.4k 收藏
点赞数
分类专栏： XML 文章标签： openxml sqlserver attributes xml output pascal
定xml节点有如下定义


    <?xml version="1.0" ?>
    <Result>
        <ad id="3471">
            <Item Time="953" Frames="122" TimeStamp="2007-09-21 14:21:27" AvgArea="53.577499" />
        </ad>
        <ad id="3472">
            <Item Time="20281" Frames="1899" TimeStamp="2007-09-21 14:20:03" AvgArea="99.947342" />
        </ad>
        <ad id="3486">
            <Item Time="12829" Frames="254" TimeStamp="2007-09-21 14:20:16" AvgArea="99.606300" />
        </ad>
        <ad id="3509">
            <Item Time="10312" Frames="979" TimeStamp="2007-09-21 14:21:00" AvgArea="38.480518" />
            <Item Time="40469" Frames="632" TimeStamp="2007-09-21 14:21:07" AvgArea="31.995926" />
        </ad>
        <ad id="3514">
            <Item Time="7484" Frames="692" TimeStamp="2007-09-21 14:20:24" AvgArea="0.641638" />
            <Item Time="2484" Frames="264" TimeStamp="2007-09-21 14:21:00" AvgArea="0.846187" />
            <Item Time="40501" Frames="632" TimeStamp="2007-09-21 14:21:07" AvgArea="0.858317" />
        </ad>
        <ad id="3521">
            <Item Time="6219" Frames="626" TimeStamp="2007-09-21 14:21:07" AvgArea="35.246521" />
        </ad>
    </Result>

上述的xml文件节点表达的逻辑结构为：根节点Result下存在若干个ad节点，每个ad节点下含有若干个Item节点。

现有需求将以上节点平展成一张二维表：

    T(
        ADID varchar(10), --ad.id,
        ShowTimes int,-- Item.Time,
        Frames int,-- Item.Frames,
        StatTime datetime,-- Item.TimeStamp,
        AvgArea decimal(20,4)-- Item.AvgArea
    )

脚本组织如下：


    -- Create an internal representation of the XML document.
    DECLARE @idoc int
    EXEC sp_xml_preparedocument @idoc OUTPUT, '
    <Result>
        <ad id="3471">
            <Item Time="953" Frames="122" TimeStamp="2007-09-21 14:21:27" AvgArea="53.577499" />
        </ad>
        <ad id="3472">
            <Item Time="20281" Frames="1899" TimeStamp="2007-09-21 14:20:03" AvgArea="99.947342" />
        </ad>
        <ad id="3486">
            <Item Time="12829" Frames="254" TimeStamp="2007-09-21 14:20:16" AvgArea="99.606300" />
        </ad>
        <ad id="3509">
            <Item Time="10312" Frames="979" TimeStamp="2007-09-21 14:21:00" AvgArea="38.480518" />
            <Item Time="40469" Frames="632" TimeStamp="2007-09-21 14:21:07" AvgArea="31.995926" />
        </ad>
        <ad id="3514">
            <Item Time="7484" Frames="692" TimeStamp="2007-09-21 14:20:24" AvgArea="0.641638" />
            <Item Time="2484" Frames="264" TimeStamp="2007-09-21 14:21:00" AvgArea="0.846187" />
            <Item Time="40501" Frames="632" TimeStamp="2007-09-21 14:21:07" AvgArea="0.858317" />
        </ad>
        <ad id="3521">
            <Item Time="6219" Frames="626" TimeStamp="2007-09-21 14:21:07" AvgArea="35.246521" />
        </ad>
    </Result>'
   
    -- Execute a SELECT statement using OPENXML rowset provider.
    SELECT *
    FROM OPENXML (@idoc, '/Result/ad/Item', 1)
    WITH (ADID varchar(10) ,
        ShowTimes int ,
        Frames int,
        StatTime datetime ,
        AvgArea decimal(20,4)
    )

    --free the document
    EXEC sp_xml_removedocument @idoc

使用OPENXML()需要一个准备动作：定义一个文档指针@idoc，类型为int（sp_xml_preparedocument）。使用完后还需要把它释放掉（sp_xml_removedocument）。

OPENXML()本身返回一个行集，并且需要3个参数和一组列映射定义。
3个参数分别是：预先准备好的文档指针@idoc，搜索路径起点，映射标志。

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

这些标志可以组合使用，比如选择3表示 XML_ATTRIBUTES + XML_ATTRIBUTES，如果with子句中的路径描述是完整的，那么这个标志实际上并没有作用。

with子句中的路径(ColPattern)描述：


    With (ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...] )
就是一个ColPattern描述，..表示取当前深度(Result/ad/Item)的上一级，也就是Result/ad；/@id表示取这个节点的名为id的ATTRIBUTE值。是专用于ATTRIBUTE的前缀符号。

除了取ATTRIBUTE值以外，还可以取ELEMENT值。ColPattern中的无前缀标识表示ELEMENT，如果有定义:

    <A ID="1">
        <B>this is elementB</B>
    </A>

那么想要取到this is elementB这个值的话，脚本需要写成：

    DECLARE @idoc int
   
    EXEC sp_xml_preparedocument @idoc OUTPUT, '
    <A ID="1">
        <B>this is elementB</B>
    </A>'
   
    SELECT *
    FROM OPENXML (@idoc, 'A/B', 0)
    WITH (B varchar(20) '../B')

    EXEC sp_xml_removedocument @idoc

ColPattern中还有一种标识：，用于返回XMLDOM中的一些元属性值(Meta Property)，比如@mp:id表示当前元素在DOM中的唯一标识；@mp:parentid表示当前元素的父节点标识。比如

    DECLARE @idoc int
   
    EXEC sp_xml_preparedocument @idoc OUTPUT, '
    <A ID="1">
        <B>this is elementB</B>
    </A>'
   
    SELECT *
    FROM OPENXML (@idoc, 'A/B', 0)
    WITH (
        curid varchar(20) ,
        parid varchar(20)
    )

    EXEC sp_xml_removedocument @idoc

可被选择的Meta Property有：@mp:id Provides system-generated, document-wide identifier of the DOM node. An XML ID of 0 indicates that the element is a root element and its @mp:parentid is NULL.
@mp:parentid Same as @mp:id, only for the parent.
@mp:localname Provides the non-fully qualified name of the node. It is used with prefix and namespace URI to name element or attribute nodes.
@mp:parentlocalname Same as @mp:localname, only for the parent.
@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present.
@mp:parentnamespaceuri Same as @mp:namespaceuri, only for the parent.
@mp:prefix The namespace prefix of the current element name.
@mp:prev Stores the @mp:id of the previous sibling relative to a node. Using this, we can tell something about the ordering of the elements at the current level of the hierarchy.
@mp:xmltext This Meta property is used for processing purposes, and contains the actual XML text for the current element as used in the overflow handling of OPENXML.


这些元属性值实际上都属于一个“元属性表”，官方名称：边缘表。边缘表可以通过省略WITH子句的方式获取：

    DECLARE @idoc int
   
    EXEC sp_xml_preparedocument @idoc OUTPUT, '
    <A ID="1">
        <B>this is elementB</B>
    </A>'
   
    SELECT *
    FROM OPENXML (@idoc, 'A/B', 0)

    EXEC sp_xml_removedocument @idoc

各列的含义与上表一一对应。

映射标志有什么用？
前文提到OPENXML()的3个参数分别是：预先准备好的文档指针@idoc，搜索路径，映射标志。...如果with子句中的路径描述是完整的，那么这个(映射)标志实际上并没有作用。

with子句中的路径描述ColPattern是允许不提供的（而且With子句中允许提供一个表作为它的列定义，此时ColPattern是不存在的），这个时候就会根据ColName的名称去自动寻找合适的元素，如果这时候存在同名的两个ELEMENT和ATTRIBUTE的话，就需要一个优先级仲裁，这就是映射标志的作用。

    DECLARE @idoc int
   
    EXEC sp_xml_preparedocument @idoc OUTPUT, '
    <A B="1">
        <B>this is elementB</B>
    </A>'
   
    SELECT *
    FROM OPENXML (@idoc, 'A', 1)
    WITH (
        B varchar(20)
    )

    SELECT *
    FROM OPENXML (@idoc, 'A', 2)
    WITH (
        B varchar(20)
    )

    EXEC sp_xml_removedocument @idoc

在这个特定的环境下，映射标志的不同导致了结果的不同。在OPENXML函数中分析的xml文本是大小写敏感的，所以如果有良好的命名习惯应该不至于会依靠这个映射标志来确定返回值。我的命名习惯包括：不用单字母作名称，表名全大写，列名用Pascal格式，变量名用camel格式。

以上内容很多是个人理解和实验结果，有错误大家不吝指出。


tianlianchao1982
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
关于SqlServer中openxml的使用
wateruning的专栏
10-22 2289
A:active { FONT-FAMILY: "Arial,Helvetica,sans-serif,新宋体";COLOR: #0000ff; FONT-SIZE: 8pt; TEXT-DECORATION: none }A:visited { FONT-FAMILY: "Arial,Helvetica,sans-serif,新宋体";COLOR: #444400
OPENXML
eva_xiao的专栏
12-25 596
这几天一直在学习SQLSERVER2008，希望能把自己学到的一点东西整理出来，供自己没事的时候翻阅，如果也能偶尔照顾到某位程序员，那就更nice。    XML作为数据传输一个非常优秀的应用，越来越受到业界的追捧。我们看到sqlserver从2000开始一直在增强其对xml的性能。在sqlserver2008中，有两个很重要的 语句或者函数：for xml,和openxml。这两个执行的功能
OpenXML文档,sqlserver处理XML,可用于存储过程数组传参
12-01
利用OpenXML获取XML文件中的数据 语法 OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)] 参数。。。。。
sqlserver openxml 函数在达梦中实现
weixin_39018564的博客
09-24 532
sqlserver to 达梦
SQL Server解析XML数据
vvull的博客
03-06 508
因为IT说SQL Server兼容级别高影响性能，所以改用XMLOPENXML (SQL Server)OPENXML (Transact-SQL)
SQL Server中读取XML文件的简单做法
03-03
这种做法为开发者提供了一种灵活的方式，使他们能够在SQL Server环境中直接对XML数据进行操作，而无需额外的导入步骤。不过，值得注意的是，这种方法可能不适用于大规模的XML文件，因为它涉及多次内存操作。在SQL ...
在SQL Server 2005中使用XML数据.pdf
09-19
此外，SQL Server 2005还提供了`FOR XML`子句和`OPENXML`函数的增强功能，使得XML数据的导出和导入更加方便。`FOR XML`子句用于构建XML格式的结果集，而`OPENXML`函数则用于解析XML文档并生成行集，以便进行处理。 ...
如何将数据从CSV和XML导入MS SQL Server
04-08
导入XML数据到SQL Server，可以使用`OPENXML`函数（旧方法），但现在推荐使用`xml`数据类型的方法，这允许直接将XML文档作为一个变量处理。例如： ```sql DECLARE @xmlDoc xml = N'<root><row ID="1" Name="John" ...
SQLServer中XML应用
12-30
随着 SQL Server 2000 的推出，Transact-SQL 语言中新增了 `FOR XML` 和 `OPENXML` 关键字，允许开发者通过编写 Transact-SQL 代码来生成 XML 格式的查询结果或导入 XML 文档。 到了 SQL Server 2005 版本，微软...
SQLSERVER 2005中使用sql语句对xml文件和其数据的进行操作(很全面)
09-11
导入XML数据时，可以使用`sp_xml_preparedocument`和`openxml`结合`WITH`子句来解析XML文档并将其内容插入到表中。在示例中，分别创建了两个表`Xmltable`和`Xmlta`，并用不同路径的XML节点数据填充它们。 ```sql --...
OPENXML第三个参数解疑
08-09
OPENXML第三个参数解疑 OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) [ WITH ( SchemaDeclaration | TableName ) ] idoc XML 文档的内部表式形式的文档句柄。 通过调用 sp_xml_preparedocument 创建 XML 文档的内部表式形式。 rowpattern XPath 模式，用来标识要作为行处理的节点。 flags 指示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。 flags 为可选输入参数，可以是下列值之一（0、1、2、8）
XML与SQL Server 2000的数据转换.pdf
09-19
例如，可以使用ADO的Recordset对象将SQL查询结果转换为XML，或者将XML数据导入到SQL Server的表中。 **SQL Server 2000对XML的支持** SQL Server 2000为XML提供了多种内置功能，如XML数据类型、XML索引和支持XML的...
SQL链接和操作XML文件
06-07
1. **导入XML文件**：你可以使用`OPENROWSET(BULK...)`函数或者`BULK INSERT`语句将XML文件导入到数据库表中。例如： ```sql BULK INSERT YourTable FROM 'C:\path\to\your\file.xml' WITH (FORMATFILE = 'C:\...
使用OpenXML和ADO.NET将数据插入SQL 2000
04-11
标题中的“使用OpenXML和ADO.NET将数据插入SQL 2000”指的是利用Microsoft Office Open XML格式（OpenXML）的API以及ADO.NET框架来实现数据导入到SQL Server 2000数据库的过程。OpenXML是一种开放标准，用于存储和...
SQL openxml用法
jack15850798154的专栏
03-01 7799
                                                                              openxml用法2008-11-06 15:43 OPENXML 通过 XML 文档提供行集视图。由于 OPENXML 是行集提供程序，因此可在会出现行集提供程序（如表、视图或 OPENROWSET 函数）
SQL Server操作XML(三)OPENXML函数功能
dbc64549的博客
06-12 1011
declare @xdoc xml; SET @xdoc =' <ROOT> <Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"&gt...
将数据从XML文件导入和处理到SQL Server表中
最新发布
2301_76752951的博客
03-12 476
将数据从XML文件导入和处理到SQL Server表中
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值