November 25, 2010 at 5:00 pm
I am coding procedures which extract XML that is returned to a client application.
The following code is a quick example on what I am trying to do:
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 [Person!2!Name!ELEMENT]
,NULL as [Person!2!Name_Changed!ELEMENT]
,NULL as [Person!2!DateStarted!ELEMENT]
,NULL as [Person!2!DateStarted_Changed!ELEMENT]
UNION
select
2 as [tag]
,1 as [parent]
,NULL as [PeopleTable!1!root]
,newtab.id as [Person!2!ID]
,newtab.name as [Person!2!Name!ELEMENT]
,case when newtab.name = oldtab.name then 'N' else 'Y' end as [Person!2!Name_Changed!ELEMENT]
,newtab.datestarted as [Person!2!DateStarted!ELEMENT]
,case when newtab.datestarted = oldtab.datestarted then 'N' else 'Y' end as [Person!2!DateStarted_Changed!ELEMENT]
from @temptabnew newtab
join @temptabold oldtab
on newtab.id = oldtab.id
FOR XML EXPLICIT
The following is the XML generated:
<PeopleTable>
<Person ID="1">
<Name>John Smith</Name>
<Name_Changed>N</Name_Changed>
<DateStarted>2000-01-01T00:00:00</DateStarted>
<DateStarted_Changed>N</DateStarted_Changed>
</Person>
<Person ID="2">
<Name>Jack Black</Name>
<Name_Changed>N</Name_Changed>
<DateStarted>2002-02-01T00:00:00</DateStarted>
<DateStarted_Changed>Y</DateStarted_Changed>
</Person>
<Person ID="3">
<Name>Bill Bob</Name>
<Name_Changed>Y</Name_Changed>
<DateStarted>2005-03-06T00:00:00</DateStarted>
<DateStarted_Changed>N</DateStarted_Changed>
</Person>
</PeopleTable>
What I would like to do, however, is move the Changed elements into attributes of the child elements:
<PeopleTable>
<Person ID="1">
<Name Changed="N">John Smith</Name>
<DateStarted Changed="N">2000-01-01T00:00:00</DateStarted>
</Person>
<Person ID="2">
<Name Changed="N">Jack Black</Name>
<DateStarted Changed="Y">2002-02-01T00:00:00</DateStarted>
</Person>
<Person ID="3">
<Name Changed="Y">Bill Bob</Name>
<DateStarted Changed="N">2005-03-06T00:00:00</DateStarted>
</Person>
</PeopleTable>
Is this possible?
August 20, 2012 at 5:58 am
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!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply