FOR XML EXPLICIT add attribute to element

• david.rowland

  SSC-Addicted

  Points: 484

  More actions

  November 25, 2010 at 5:00 pm

  #227199

  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?

• handkot

  SSCertifiable

  Points: 5181

  More actions

  August 20, 2012 at 5:58 am

  #1527535

  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!