`
zz8ss5ww6
  • 浏览: 64462 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

记一次复杂的sql server xml字段查询

SQL ServerXMLSQLITeye活动
阅读更多
项目中一直使用”no sql”的方式将entity序列化成xml格式存储在sql server数据库表的xml字段中，最近终于要对数据进行迁移了，需要将数据迁移回关系型格式，因此也经历了一次比较有趣而又复杂的xml查询。
具体问题是这样的。数据库中有一张EntityState表用于存储应用中所有被xml序列化的entity，表结构如下图所示：

其中的Xml字段存储了entity序列化之后的xml内容，Tag字段为entity的类型码，是由entity类型经过哈希之后计算出来的。
有一个名为CalendarMonth的Entity，存储了用户一个月每天的活动信息。一个具体的CalendarMonth的xml字段中可能存储了如下的内容： 
<Entity CLR_TYPE="TaxCalendar.Model.CalendarMonth" completeStatue="future" month="6" userId="ef5f1db5-fd67-40fd-b533-7ee21474cae6" year="2011">
  <List Key="days">
    <Dictionary Index="0" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="1" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="1" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="2" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="2" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="3" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="3" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="4" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="4" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="5" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="5" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="6" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="6" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="7" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="7" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="8" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="8" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="9" secondActivity="Work">
      <Null Key="location" />
    </Dictionary>
    <Dictionary Index="9" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="10" location="9e4b3b25-6bc2-460a-8793-988a6bf2fb48" secondActivity="Work" />
    <Dictionary Index="10" activity="National" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="11" secondActivity="Vacation">
      <Null Key="location" />
    </Dictionary>
    <Dictionary Index="11" activity="Other" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="12" location="9e4b3b25-6bc2-460a-8793-988a6bf2fb48" secondActivity="Other" />
    <Dictionary Index="12" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="13" secondActivity="Work">
      <Null Key="location" />
    </Dictionary>
    <Dictionary Index="13" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="14" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="14" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="15" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="15" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="16" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="16" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="17" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="17" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="18" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="18" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="19" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="19" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="20" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="20" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="21" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="21" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="22" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="22" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="23" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="23" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="24" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="24" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="25" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="25" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="26" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="26" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="27" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="27" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="28" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="28" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="29" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
    <Dictionary Index="29" activity="Work" CLR_TYPE="TaxCalendar.Model.CalendarDay" date="30" location="54f045e0-9cd9-4eae-8c12-3ed5713b6260" secondActivity="Work" />
  </List>
</Entity>

可以看到该CalendarMonth 的xml字段记录了一个用户2011年6月份每一天的活动情况。
而新数据库中以关系数据的方式按日为单位存储用户每天的活动情况，它的表结构如下图所示：

新数据表字段与xml内容的对应关系是：
FirstActivities中的字段EntityState中xml字段中的内容
Id无对应，由新数据表在插入记录时自动生成
UserIdEntity的userId属性
Date由Entity的year属性确定年份，month属性确定月份，其下每个Dictionary的date属性确定日期，最后拼成一个日期字段
Activity对应到每个Dictionary的activity属性
Timestamp无对应内容，由新数据表在插入记录时根据当前时间生成

我们要实现的任务是：根据对应关系，构造sql查询将xml中的内容“适配”成新数据库表FirstActivities对应字段中。这里面有几个难点：
  • 原表中的一条记录（也就是一条xml字段的内容）在新表中变成了n条记录。
  • 新表中每一行的值既需要从xml中根节点Entity中取，又需要从子节点Dictionary中取得。

下面分几步来实现这个查询sql。

第一步: 从子节点中查询每一天的日期（这里单指日）和活动内容。
直接上sql： 
select
days.content.value('./@date', 'int') as day, -- (1) 
days.content.value('./@activity', 'nvarchar(100)') as Activity
from entitystate
cross apply -- (2)
entitystate.xml.nodes('/Entity/List/Dictionary') as days(content) -- (3) 
where tag = 1913201649 –- CalendarMonth Type tag value

先看（2）。这里需要将一条xml记录拆分成n条记录，因此可以使用cross apply。按照微软官方的说法，Cross Apply使表可以和表值函数结果进行join, 这样表值函数的参数就可以使用一个结果集，而不是一个标量值。
再看（3）。这里用到了nodes()方法，它的语法是： 
nodes (XQuery) as Table(Column)

结果是一个包含原始 XML 实例的逻辑副本的行集。
我们这里查询xml内容中所有的/Entity/List/Dictionary节点，并放到表days的content字段中。
最后看（1）。这里使用了sql server xml数据类型的value()方法，该方法应用于 nodes() 所返回的行集，从原始 XML 实例中检索多个值。它的语法是： 
value (XQuery, SQLType)

我们这里使用了两次value方法分别得到了日期和活动内容。
这一步的查询结果如下图所示。


第二步：从xml根节点中查询year，month和userId。 
select 

months.content.value('./@userId','uniqueidentifier') as UserId,
months.content.value('./@year', 'int') as year,
months.content.value('./@month', 'int') as month,

days.content.value('./@date', 'int') as day,
days.content.value('./@activity', 'nvarchar(100)') as Activity
from entitystate
cross apply
entitystate.xml.nodes('/Entity/List/Dictionary') as days(content)

cross apply
entitystate.xml.nodes('/Entity') as months(content)

where tag=1913201649

这一次我们在第一步结果的基础上再次使用cross apply连接nodes()方法查询xml中的/Entity节点，并使用value()方法得到需要的UserId，year和month。经过这两步我们得到如下的查询结果：


第三步：组装最后的结果。
现在已经离我们的适配最终结果很近了，只需要把年月日组装成日期，再加上自动生成的id和Timestamp就可以了。 
select
NewId() as Id, -- (1)
UserId,
CAST(
      CAST(year AS VARCHAR(4)) +
      RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
      RIGHT('0' + CAST(day AS VARCHAR(2)), 2) 
   AS DATETIME) as Date, -- (2)
Activity,
CURRENT_TIMESTAMP as Timestamp -- (3)
from
(select 
months.content.value('./@userId','uniqueidentifier') as UserId,
months.content.value('./@year', 'int') as year,
months.content.value('./@month', 'int') as month,
days.content.value('./@date', 'int') as day,
days.content.value('./@activity', 'nvarchar(100)') as Activity
from entitystate
cross apply
entitystate.xml.nodes('/Entity/List/Dictionary') as days(content)
cross apply
entitystate.xml.nodes('/Entity') as months(content)
where tag=1913201649) 
as FirstActivityRawTable -- (4)

（1）这里我们使用了sql  server的NewId()方法自动生成uniqieidentifier字段的值。
（2）这里反复使用了Cast方法将年月日拼成了日期字段。
（3）这里使用CURRENT_TIMESTAMP得到当前的timestamp值。
（4）将原来的查询包含在一个子查询里。
最终我们终于得到了如下结果：

大功告成！！
0
顶
2
踩
分享到：
| Castle ActiveRecord中Inverse对Delete的 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    提高sql server xml 字段的查询速度

    提高sql server xml字段的查询速度的几种方法，其中使用索引关键字方法在模糊查询中相当实用。

    SQL Server中读取XML文件的简单做法

    SQL Server 2000使得以XML导出数据变得更加简单，但在SQL Server 2000中导入XML数据并对其进行处理则有些麻烦。文中介绍了SQL Server中读取XML文件的简单方法。有了这种解决办法，对XML文档进行各种处理就成为了可能...

    对XML字段进行约束 SQL server 2005

    对XML字段进行约束 SQL server 2005 xml schema

    Sqlserver2005XML优化知识

    sqlserver2005 关于xml字段优化的一些总结。包括select中怎么使用。

    SQL Server解析XML数据的方法详解

    主要介绍了SQL Server解析XML数据的方法,结合实例形式详细分析了SQL Server针对xml数据的读取,遍历,删除,查找等常用操作技巧,具有一定参考借鉴价值,需要的朋友可以参考下

    SQL Server将一列的多行内容拼接成一行的实现方法

    昨天遇到一个SQL Server的问题：需要写一个储存过程来处理几个表中的数据，最后问题出在我想将一个表的一个列的多行内容拼接成一行，比如表中有两列数据 ： 类别 名称 AAA 企业1 AAA 企业2 AAA 企业3 ...

    SqlServer下通过XML拆分字符串的方法

    set @doc=cast(‘&lt;Root&gt;&lt;item&gt;’+replace(@SelectedProjectArray,’,’,'&lt;/ProjID&gt;&lt;/item&gt;&lt;item&gt;&lt;ProjID&gt;’)+'&lt;/ProjID&gt;&lt;/item&gt;’ as xml) EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc SELECT ProjID FROM ...

    SQLServer 高级应用特性学习大纲（完整版）

    SQL Server 2005 核心技术 SQL Server 2005 服务简介 SSAS（Analysis Service）与 商业智能（BI） 数据挖掘算法简介（Data Mining） 联机数据分析技术（OLAP） SSIS （Integration Service）与商业智能（BI） SSRS...

    Microsoft SQL Server 2005 Express Edition SP3

    Microsoft SQL Server 2005 Express Edition (SQL Server Express) 是一个免费且易于使用的 SQL Server 2005 版本，它替换了 Microsoft Desktop Engine (MSDE)。与 Microsoft Visual Studio 2005 集成之后，SQL ...

    Sqlserver2000经典脚本

    介绍就不多说了，下边是部分目录，觉得有用的话就顶一个 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整...

    XML操作函数详解-SQL Server 2005

    XML操作函数详解-SQL Server 2005中,XML字段的操作

    sqlserver2005 xml字段的读写操作

    sqlserver2005 有xml字段了，下面是关于xml字段的相关读写操作

    把Oracle或SQLServer表中数据转换成XML的工具

    用JSP、Servlet、Oracle、SQLServer写的工具，...可以选择两种数据库连接：Oracle或SQLServer，然后指定Oracle中的用户或SQLServer中的库，以及相应的表、字段，按XSL定义的XML样式预览要生成的XML，然后导出成XML文件

    SQL Server 2008管理员必备指南(超高清PDF)Part3

    4.3.1 使用SQL Server Management Studio查询 4.3.2 执行查询和改变设置 4.3.3 检查和设置配置参数 4.3.4 使用ALTER DATABASE改变设置 第Ⅱ部分 SQL Server 2008的系统管理 第5章 管理企业 5.1 使用SQL Server ...

    SQL Server 2008管理员必备指南(超高清PDF)Part1

    4.3.1 使用SQL Server Management Studio查询 4.3.2 执行查询和改变设置 4.3.3 检查和设置配置参数 4.3.4 使用ALTER DATABASE改变设置 第Ⅱ部分 SQL Server 2008的系统管理 第5章 管理企业 5.1 使用SQL Server ...

    SQL2008 详解直接将XML存入到SQL中

    随着SQL Server 对XML字段的支持，相应的，T-SQL语句也提供了大量对XML操作的功能来配合SQL Server中XML字段的使用。本文主要说明如何使用SQL语句对XML进行操作。 二、定义XML字段  在进行数据库的设计中，我们可以...

    SQL SERVER数据库批量更新程序 1.0.0.33

    (类似SQLSERVER BCP工具，支持在批量复制之前或者之后同步执行特定的语句)。 5.支持将查询结果转换为sql语句。 6.支持批量对多个sql server数据库的特定用户修改密码。（随机密码） 7.支持同时对多个windows主机进行...

    SQL Server 2008管理员必备指南(超高清PDF)Part2

    4.3.1 使用SQL Server Management Studio查询 4.3.2 执行查询和改变设置 4.3.3 检查和设置配置参数 4.3.4 使用ALTER DATABASE改变设置 第Ⅱ部分 SQL Server 2008的系统管理 第5章 管理企业 5.1 使用SQL Server ...

    Sqlserver 工具源码 C# WinForm

    1、能够快速提取表的字段列表。 2、根据字段备注搜索字段 3、根据特定内容搜索 存储过程、视图、函数的关联使用。 4、生成实体类 5、生成数据字典 6、软件使用devexpress 控件。对开发devexpress表格控件，能直接...

Magicbox
Global site tag (gtag.js) - Google Analytics