delete output into <xml column of audit table>

[cc]

Currently running Sql Server 2005

Is it possible to issue the delete command and capture the affected rows as
xml types that will be stored in an audit table with an xml column?

Something along the lines of:

delete from source_table
output
(deleted.*
into audit_table (xml_audit_colu mn)
for xml auto)
where source_table.co lumn = @delete_value

[Plamen Ratchev]

Re: delete output into &lt;xml column of audit table&gt;

You cannot do it in a single statement because the OUTPUT clause does not
support subqueries. One way is to get the deleted rows into a temp table and
then transfer as XML to your audit table.

Here is one example of implementing this in a trigger:

http://blogs.sqlservercentral.com/prosqlxml/archive/2008/01/26/create-a-dynamic-logging-trigger-with-xml.aspx

HTH,

Plamen Ratchev

http://www.SQLStudio.com