TSQL Updating XML Variable 1

[T0AD]

Hello.

I have the following code in a SQL trigger:

SELECT @XML = SELECT * FROM inserted FOR XML PATH ('MyEntity'), ELEMENTS, ROOT('Inserted'), TYPE);

I want to add the deleted records to this too, but my code below just overwrites the @XML variable. How do I add to it?

SELECT @XML = SELECT * FROM deleted FOR XML PATH ('MyEntity'), ELEMENTS, ROOT('Deleted'), TYPE);

Many thanks in advance,
Pete

There's a thin line between genius, and insanity!

 

[starsky51]

You could cast each return value to string, concatenate and then cast it back to xml:

SELECT @XML =
CAST(CAST((SELECT * FROM inserted FOR XML PATH ('MyEntity'), ELEMENTS, ROOT('Inserted'), TYPE) AS varchar(max))
+ CAST((SELECT * FROM deleted FOR XML PATH ('MyEntity'), ELEMENTS, ROOT('Deleted'), TYPE) AS varchar(max))
 AS xml);

 

[T0AD]

Didn't think of that, thanks!

There's a thin line between genius, and insanity!