InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 23:31:10 GMT20http://www.sqlservercentral.com/Forums/Topic1026597-392-1.aspx[quote]Better late than never[/quote][code="sql"]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] UNIONselect 2 as [tag] ,1 as [parent] ,NULL ,newtab.id ,NULL ,NULL ,NULL ,NULLfrom @temptabnew newtab join @temptabold oldtab on newtab.id = oldtab.idUNIONselect 3 as [tag] ,2 as [parent] ,NULL ,newtab.id ,newtab.name ,case when newtab.name = oldtab.name then 'N' else 'Y' end ,NULL ,NULLfrom @temptabnew newtab join @temptabold oldtab on newtab.id = oldtab.idUNIONselect 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 waySelect ( 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 [/code]Mon, 20 Aug 2012 05:58:54 GMThandkothttp://www.sqlservercentral.com/Forums/Topic1026597-392-1.aspxI 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:[code="sql"]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]UNIONselect 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.idFOR XML EXPLICIT[/code]The following is the XML generated:[code="xml"]<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>[/code]What I would like to do, however, is move the [b]Changed[/b] elements into attributes of the child elements:[code="xml"]<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>[/code]Is this possible?Thu, 25 Nov 2010 17:00:01 GMTdavid.rowland