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

项目中一直使用”no sql”的方式将entity序列化成xml格式存储在sql server数据库表的xml字段中，最近终于要对数据进行迁移了，需要将数据迁移回关系型格式，因此也经历了一次比较有趣而又复杂的xml查询。
具体问题是这样的。数据库中有一张EntityState表用于存储应用中所有被xml序列化的entity，表结构如下图所示：
[img]http://dl.iteye.com/upload/picture/pic/92096/0d0f0d9f-0ba5-369e-861d-2f52bb3e124a-thumb.png[/img]
其中的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月份每一天的活动情况。
而新数据库中以关系数据的方式按日为单位存储用户每天的活动情况，它的表结构如下图所示：
[img]http://dl.iteye.com/upload/picture/pic/92098/837e9932-7f49-3bbf-8cba-fe8a01408de2-thumb.png[/img]
新数据表字段与xml内容的对应关系是：
[table]
|FirstActivities中的字段|EntityState中xml字段中的内容|
|Id|无对应，由新数据表在插入记录时自动生成|
|UserId|Entity的userId属性|
|Date|由Entity的year属性确定年份，month属性确定月份，其下每个Dictionary的date属性确定日期，最后拼成一个日期字段|
|Activity|对应到每个Dictionary的activity属性|
|Timestamp|无对应内容，由新数据表在插入记录时根据当前时间生成|
[/table]
我们要实现的任务是：根据对应关系，构造sql查询将xml中的内容“适配”成新数据库表FirstActivities对应字段中。这里面有几个难点：
[list]
[*]原表中的一条记录（也就是一条xml字段的内容）在新表中变成了n条记录。
[*]新表中每一行的值既需要从xml中根节点Entity中取，又需要从子节点Dictionary中取得。
[/list]
下面分几步来实现这个查询sql。

[b]第一步: 从子节点中查询每一天的日期（这里单指日）和活动内容。[/b]
直接上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方法分别得到了日期和活动内容。
这一步的查询结果如下图所示。
[img]http://dl.iteye.com/upload/picture/pic/92100/475ba417-aeb5-393b-b209-4c4852c644b0-thumb.png[/img]

[b]第二步：从xml根节点中查询year，month和userId。[/b]

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。经过这两步我们得到如下的查询结果：
[img]http://dl.iteye.com/upload/picture/pic/92102/67b00c81-2022-3362-8080-44d8844e74da-thumb.png[/img]

[b]第三步：组装最后的结果。[/b]
现在已经离我们的适配最终结果很近了，只需要把年月日组装成日期，再加上自动生成的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）将原来的查询包含在一个子查询里。
最终我们终于得到了如下结果：
[img]http://dl.iteye.com/upload/picture/pic/92104/4c406f2c-04d0-36e0-89ad-f0fd33db1e42-thumb.png[/img]
大功告成！！