记录历史的XML数据

Question

我在SQL Server中有一个XML列，用于存储属性的实际值和前一个值。例如，Name。

<attribute name="Name">
      <actuals>
          <element isPreferred="true" name="FirstName">Name 2</element>
          <element isPreferred="false" name="LastName">N2</element>
       </actuals>
      <previous>
          <element isPreferred="true" name="FirstName">Name 1</element>
          <element isPreferred="false" name="LastName">N1</element>
      </previous>
</attribute>

如何显示属性的历史记录，例如：

------------------------------------
Attribute | New Value | OldValue
------------------------------------
First Name| Name2     | Name1
Last Name | N2        | N1
------------------------------------

属性可能会有所不同，并且可以只有单个元素，例如性别，或者有多个元素，例如名称或地址（addressLine1、city、state、country）。

<attribute name="Gender">
      <actuals>
          <element isPreferred="true" name="Gender">Male</element>             
       </actuals>
      <previous>
          <element isPreferred="true" name="Gender">Other</element>     
      </previous>
</attribute>

- Biju Thomas

Answer 1

试一下这个。

DECLARE @xml1 XML='<attribute name="Name">
      <actuals>
          <element isPreferred="true" name="FirstName">Name 2</element>
          <element isPreferred="false" name="LastName">N2</element>
       </actuals>
      <previous>
          <element isPreferred="true" name="FirstName">Name 1</element>
          <element isPreferred="false" name="LastName">N1</element>
      </previous>
</attribute>'

SELECT Attribute=[Xml_Tab].[Cols].value('(actuals/element/@name)[1]', 'varchar(50)'),
       [New Value]=[Xml_Tab].[Cols].value('(actuals/element)[1]', 'varchar(50)'),
       OldValue=[Xml_Tab].[Cols].value('(previous/element)[1]', 'varchar(50)')
FROM   @xml1.nodes('/attribute')AS [Xml_Tab]([Cols])
UNION
SELECT [Xml_Tab].[Cols].value('(actuals/element/@name)[2]', 'varchar(50)'),
       [Xml_Tab].[Cols].value('(actuals/element)[2]', 'varchar(50)'),
       [Xml_Tab].[Cols].value('(previous/element)[2]', 'varchar(50)')
FROM   @xml1.nodes('/attribute')AS [Xml_Tab]([Cols]) 

- Pரதீப்

---

谢谢，对于Name来说它是有效的，但Attributes可能有多个元素，例如Address（addressLine1，city，state，country）。 - Biju Thomas

Answer 2

你可以查询实际和之前的节点，然后像这样连接它们：

;with cte_act as (
    select
        t.c.value('@name', 'nvarchar(128)') as [Attribute],
        t.c.value('.', 'nvarchar(128)') as [Value]
    from @data.nodes('/attribute/actuals/element') as t(c)
), cte_prev as (
    select
        t.c.value('@name', 'nvarchar(128)') as [Attribute],
        t.c.value('.', 'nvarchar(128)') as [Value]
    from @data.nodes('/attribute/previous/element') as t(c)
)
select
    act.[Attribute],
    act.[Value] as [New Value],
    prev.[Value] as [Old Value]
from cte_act as act
    left outer join cte_prev as prev on prev.[Attribute] = act.[Attribute]

SQL Fiddle演示

或者你可以像使用XQuery重新格式化XML一样做一些疯狂的事情，然后进行查询：

;with cte as (
    select
        t.c.query('
            for $act in actuals/element
                return <element name="{$act/@name}" newvalue="{$act/text()}" oldvalue="{($act/../../previous/element[@name=$act/@name])[1]/text()}"/>
        ') as data
    from <your table> as d
        outer apply d.data.nodes('/attribute') as t(c)
)
select
    t.c.value('@name', 'nvarchar(128)') as [Attribute],
    t.c.value('@newvalue', 'nvarchar(128)') as [New Value],
    t.c.value('@oldvalue', 'nvarchar(128)') as [Old Value]
from cte as d
    outer apply data.nodes('element') as t(c)

SQL Fiddle演示

或者，像这样查询所有actual元素，并尝试获取每个实际元素的previous：

select
    t.c.value('@name', 'nvarchar(128)') as [Attribute],
    t.c.value('.', 'nvarchar(128)') as [Value],
    t.c.value('let $name:=@name return (../../previous/element[@name=$name])[1]', 'nvarchar(128)') as [Value]
from @temp as d
    outer apply d.data.nodes('/attribute/actuals/element') as t(c)

SQL Fiddle演示

- Roman Pekar

---

谢谢Roman。您能否建议一下，从性能角度来看哪种方法最好？ - Biju Thomas

@BijuThomas 我建议使用最后一个，但最好在你的数据上进行测试。 - Roman Pekar