delete output into <xml column of audit table>

delete output into <xml column of audit table>

am 31.03.2008 17:05:07 von 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_column)
for xml auto)
where source_table.column = @delete_value

Re: delete output into <xml column of audit table>

am 31.03.2008 17:35:00 von Plamen Ratchev

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