Attempting to convert an audit trail facility from a different DBMS to SQL2K I’ve run into a snag with XML.
What I’m attempting to duplicate as a process where an Update or Delete trigger fires for a table and the logic called takes the entire contents of the pre-update or pre-delete record, converts it to an XML document, and inserts that document into a large VARCHAR or TEXT field in an audit table.
At first glance, the FOR XML clause on the Select statement looked like what I needed. But I’ve run into restrictions every step of the way:
1. I can’t use FOR XML as part of a INSERT…SELECT statement.
2. I’ve tried doing a SET @memvar = and then an Insert using @memvar.