Transform Inserted and Deleted rows into Xml for audit

Question

I have the following trigger:

ALTER trigger tr_mytable_audit on mytable after insert, update, delete
as
SELECT *
FROM
    (SELECT *
     FROM inserted
     UNION
     SELECT *  
     FROM deleted) as Rows
FOR XML RAW, ELEMENTS
GO

Currently the output for inserts and deletes is as follows:

<row>
  <id>1</id>
  <column1>test</column1>
  <column2>62</column2>
</row>

Whilst for updates it is:

<row>
  <id>1</id>
  <column1>test</column1>
  <column2>62</column2>
</row>
<row>
  <id>1</id>
  <column1>changed</column1>
  <column2>62</column2>
</row>

Is it possible to transform the xml so that it would like this:

--Inserts
<row>
  <id old="" new="1"></id>
  <column1 old="" new="test"></column1>
  <column2 old="" new="62"></column2>
</row>

--Deletes
<row>
  <id old="1" new=""></id>
  <column1 old="changed" new=""></column1>
  <column2 old="62" new=""></column2>
</row>

--Updates
<row>
  <id old="1" new="1"></id>
  <column1 old="test" new="changed"></column1>
  <column2 old="62" new="62"></column2>
</row>

Better still, for updates, is it possible to leave out unchanged columns so that the xml would be just:

<row>
  <column1 old="test" new="changed"></column1>
</row>

EDIT

I managed to come up with this SQL:

ALTER trigger tr_mytable_audit on mytable after insert, update, delete
as
select d.id as 'id/old', i.id as 'id/new',
       d.column1 as 'column1/old', i.column1 as 'column1/new',
       d.column2 as 'column2/old', i.column2 as 'column2/new'
from inserted as i
  full join deleted as d
    on i.id = d.id
for xml path('row'), root('rows')

However, for updates I also have the unchanged columns. Is it possible to filter out the unchanged columns by improving on the above SQL?

Answer 1

Try joining inserted and deleted and use XML PATH to have a little more control over the XML structure to be generated. Something like this:

SELECT 
      ID = COALESCE(I.ID, D.ID)
    , Col1 = (SELECT [@old] = D.Col1, [@new] = I.Col1 
              WHERE D.Col1 <> I.Col1 OR D.Col1 IS NULL OR I.Col1 IS NULL)
    , Col2 = (SELECT [@old] = D.Col2, [@new] = I.Col2 
              WHERE D.Col2 <> I.Col2 OR D.Col2 IS NULL OR I.Col2 IS NULL)
FROM INSERTED AS I
    FULL JOIN DELETED AS D
        ON I.ID = D.ID
FOR XML PATH('row'), ROOT('rows')