Union Xml Output

• Danky 7

  SSC-Addicted

  Points: 490

  More actions

  November 26, 2010 at 3:55 pm

  #227203

  Hi,

  is there a way to do a union over two or more instances for xml???

  something like this:

  Select field1, field2

  from table1

  FOR XML RAW('Node1'), Elements

  Union

  Select field1, field2

  from table2

  FOR XML RAW('Node2'), Elements

  where field1 and field2 are the same data type.

  I tested it, but not works, but is there a way to do it?

  Thanks in advanced.

• diamondgm

  SSCrazy

  Points: 2824

  More actions

  November 29, 2010 at 12:10 am

  #1256126

  Try insert both results in to a table variable if UNION doesn't work.

• November 29, 2010 at 1:06 am

  This was removed by the editor as SPAM

• kfranken-728999

  SSC Enthusiast

  Points: 110

  More actions

  December 1, 2010 at 1:37 pm

  #1257602

  Can we assume that both tables are identical except for the output node name? The XML AUTO (or PATH) and UNION would work that way - but if the tables are different - it wouldn't.

• kfranken-728999

  SSC Enthusiast

  Points: 110

  More actions

  December 1, 2010 at 1:46 pm

  #1257604

  If the table schemas are different and a direct UNION won't work - I would suggest:

  SELECT ( SELECT field1

  , field2

  FROMtable1

  FOR XML PATH('Node1'), ELEMENTS, TYPE)

  , ( SELECTfield1

  , field2

  FROMtable1

  FOR XML PATH('Node2'), ELEMENTS, TYPE)

  FOR XML PATH('Root')