Sql Server - Capture an XML query and save it to a table?

Question

I would like to run a (extensive) query that produces one line of XML. This XML represents about 12 tables worth of relational data. When I "delete" a row from the top level table I would like to "capture" the state of the data at that moment (in XML), save it to an archive table, then delete all the child table data and finally mark the top level table/row as "isDeleted=1". We have other data hanging off of the parent table that CANNOT be deleted and cannot lose the relation to the top table.

I have most of the XML worked out - see my post here on that can of worms.

Now, how can I capture that into an archive table?

CREATE TABLE CampaignArchive(CampaignID int, XmlData XML)

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (really long list of columns) FROM (all my tables) FOR XML PATH ...

This just does not work. :)

Any suggestions?

TIA

Answer 1

You need a subquery to wrap all that XML creation into one single scalar that goes into column XmlData. Again, use TYPE to create a scalar of type XML not a string:

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (
 select (really long list of columns) 
 FROM (all my tables) FOR XML PATH ..., type);