`
zz8ss5ww6
  • 浏览: 64874 次
  • 性别: 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文件方面略显复杂，但通过`sp_xml_preparedocument`和`OPENXML`的组合，我们可以有效地读取XML文件并执行各种操作。这种做法为开发者提供了一种灵活的方式，使他们能够在SQL ...

    SQLServer中如何将一个字段的多个记录值合在一行显示

    SQLServer 中将一个字段的多个记录值合并到一行显示的实现方法 SQL Server 是一种关系型数据库管理系统，具有强大的数据处理能力和存储能力。在实际应用中，我们经常需要将一个字段的多个记录值合并到一行显示，以...

    SQLServer20052008 XML数据类型操作

    ### 一、SQL Server 2005/2008 XML 数据类型操作 #### 1. 创建表 在SQL Server中处理XML数据之前，首先需要创建一个包含`xml`数据类型的表。例如： ```sql CREATE TABLE [dbo].[xmlTable]( [id] [int] IDENTITY(1...

    SQLServer的内置XML支持

    在SQLServer中，可以将XQuery表达式嵌入到T-SQL语句中，实现对XML数据的复杂查询和操作。 五、XML和关系数据的互操作性 SQLServer提供了无缝的方式来在XML和关系数据之间进行转换。例如，你可以将关系表中的数据...

    SQL Server 2005 对Native XML Web 服务的支持

    开发者可以使用T-SQL查询语言直接操作XML字段，进行选择、插入、更新和删除等操作。 其次，SQL Server 2005提供了一个强大的XQuery引擎，允许用户通过SQL查询语言执行XQuery表达式来检索和处理XML数据。XQuery是一...

    对XML字段进行约束 SQL server 2005

    接着，我们需要在SQL Server 2005中创建一个XML Schema Collection，以便将其关联到表中的XML字段上。以下是创建名为`PersonSchema`的XML Schema Collection的SQL语句： ```sql CREATE XML SCHEMA COLLECTION ...

    sqlserver Ibatis XML自动生成工具

    SQLServer Ibatis XML自动生成工具是一款实用的开发辅助软件，主要针对Java开发人员，特别是那些在项目中使用Ibatis作为持久层框架的开发者。这款工具能够显著提高开发效率，通过自动化的方式生成Ibatis所需的XML...

    Sqlserver2005XML优化知识

    在SQL Server 2005中，XML字段的优化至...综上所述，理解并正确运用SQL Server 2005中的XML查询语法和索引策略，是优化XML字段性能的关键。合理设计和利用索引，能够显著提高XML数据的查询效率，降低数据库的运行成本。

    SQL Server XML的功能

    3. **XML方法和函数**：SQL Server提供了一系列内置的XML方法和函数，如`value()`、`exist()`、`nodes()`和`query()`，它们可以方便地在SQL查询中处理XML数据。例如，`value()`用于提取XML文档中的值，`exist()`检查...

    xml数据向sql server 2000 中转换

    SQL Server 2000是微软推出的一款关系型数据库管理系统，它支持对XML数据的处理，能够方便地进行数据存储和查询。 在“xml数据向sql server 2000 中转换”这个主题中，我们主要讨论如何利用编程技术将XML文件中的...

    sqlserver to XML

    以下是对"sqlserver to XML"这个主题的详细说明： 一、XML的基本概念 XML是一种自描述的文本格式，它使用标签来定义数据结构。与HTML不同，XML设计用于存储和传输数据，而不是显示数据。XML文档包含元素、属性、...

    sqlserver数据库类型对应Java中的数据类型

    在 SQL Server 中，xml 是一个 XML 类型，用于存储 XML 数据。在 Java 中，xml 对应的数据类型是 String。 time 在 SQL Server 中，time 是一个时间类型，用于存储时间值。在 Java 中，time 对应的数据类型是 java...

    SQL查询字段被包含语句

    `CHARINDEX`是SQL Server中的一个字符串函数，它返回一个指定的子字符串在另一个字符串中首次出现的位置。如果子字符串不存在，它将返回0。这个函数可以帮助我们一次性检查多个关键词是否包含在目标字段中。例如，要...

    将Sql Server数据库转换成Xml文件

    4. **处理复杂数据类型**：如果表中有复杂的数据类型，如图像、XML字段或其他二进制数据，你可能需要特殊处理，确保它们能正确地编码到Xml文件中。 5. **格式化Xml**：默认生成的Xml文件可能没有很好的格式，可以...

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

    此外，SQL Server还支持更复杂的XML操作，如插入、更新和删除XML数据。例如，使用 `modify()` 函数可以更新XML节点的值，`delete()` 可以删除特定节点。在处理大量XML数据时，可以使用XQuery表达式进行更高级的查询...

    FOR XML子句在SQL Server中的用法比较.pdf

    SQL Server是微软公司开发的一种关系型数据库管理系统（RDBMS），它提供了强大的数据存储、操作和查询功能。随着XML（可扩展标记语言）在数据交换中的广泛应用，SQL Server引入了FOR XML子句，以便能够将SQL查询结果...

    Professional SQL Server 2005 XML代码

    3. **XML函数和方法**：系统提供了一系列函数和方法来操作XML数据，如`SELECT FOR XML`用于将结果集转换为XML，`PARSE`和`QUERY`函数用于在XML数据上执行XQuery，`.modify()`方法用于更新XML字段等。 4. **XQuery...

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

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

    SQL Server 2005中XML数据类型的性能

    使用索引可以显著提升查询性能，特别是对于经常被查询的XML字段。有几种类型的XML索引可供选择： - **XML索引（XML Indexes）**：分为路径索引（Path Indexes）和片段索引（Fragment Indexes）。路径索引针对特定的...

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