Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FOR XML EXPLICIT add attribute to element Expand / Collapse
Author
Message
Posted Thursday, November 25, 2010 5:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 6:42 PM
Points: 26, Visits: 94
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?
Post #1026597
Posted Monday, August 20, 2012 5:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:24 PM
Points: 1,329, Visits: 470
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

Post #1347108
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse