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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy