I have one XML column in SQL Server where I store actual value and immediate previous value of attributes. eg. 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>
How can I show the history of attribute like
------------------------------------
Attribute | New Value | OldValue
------------------------------------
First Name| Name2 | Name1
Last Name | N2 | N1
------------------------------------
Attributes may vary and can have single element like gender or multiple elements like Name or Address(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>
You can query actual and previous nodes and then join them like this:
;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]
Or you can do something crazy like reformatting your xml with xquery and then query it:
;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)
Or, something like this - querying all actual
elements and for each actual element try to get 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)