Better late than never
declare @temptabnew table (id int, name varchar(50), datestarted datetime)
declare @temptabold table (id int, name varchar(50), datestarted datetime)
insert into @temptabnew values (1, 'John Smith', '2000-01-01')
insert into @temptabnew values (2, 'Jack Black', '2002-02-01')
insert into @temptabnew values (3, 'Bill Bob', '2005-03-06')
insert into @temptabold values (1, 'John Smith', '2000-01-01')
insert into @temptabold values (2, 'Jack Black', '2002-02-07')
insert into @temptabold values (3, 'Billy Bob', '2005-03-06')
select
1 as [tag]
,0 as [parent]
,NULL as [PeopleTable!1!root]
,NULL as [Person!2!ID]
,NULL as [Name!3!!ELEMENT]
,NULL as [Name!3!Changed]
,NULL as [DateStarted!4!!ELEMENT]
,NULL as [DateStarted!4!Changed]
UNION
select
2 as [tag]
,1 as [parent]
,NULL
,newtab.id
,NULL
,NULL
,NULL
,NULL
from @temptabnew newtab
join @temptabold oldtab
on newtab.id = oldtab.id
UNION
select
3 as [tag]
,2 as [parent]
,NULL
,newtab.id
,newtab.name
,case when newtab.name = oldtab.name then 'N' else 'Y' end
,NULL
,NULL
from @temptabnew newtab
join @temptabold oldtab
on newtab.id = oldtab.id
UNION
select
4 as [tag]
,2 as [parent]
,NULL
,newtab.id
,newtab.name
,case when newtab.name = oldtab.name then 'N' else 'Y' end
,newtab.datestarted
,case when newtab.datestarted = oldtab.datestarted then 'N' else 'Y' end
from @temptabnew newtab
join @temptabold oldtab
on newtab.id = oldtab.id
order by
[Person!2!ID]
,[Name!3!!ELEMENT]
,[Name!3!Changed]
,[DateStarted!4!!ELEMENT]
,[DateStarted!4!Changed]
FOR XML EXPLICIT
--other way
Select
(
Select
[@ID] = newtab.ID
, (select [@Changed] = case when newtab.name = oldtab.name then 'N' else 'Y' end, newtab.Name [*] For Xml Path('Name'), Type )
, (select [@Changed] = case when newtab.DateStarted = oldtab.DateStarted then 'N' else 'Y' end, newtab.DateStarted [*] For Xml Path('DateStarted'), Type )
From
@temptabnew newtab
join @temptabold oldtab
on newtab.id = oldtab.id
For Xml Path('Person'), Type )
For Xml Path('PeopleTable'), Type
I Have Nine Lives You Have One Only
THINK!