title: Transform Inserted and Deleted rows into Xml for audit
body: <p>I have the following trigger:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Currently the output for inserts and deletes is as follows:</p>
  
  <pre><code>&lt;row&gt;
    &lt;id&gt;1&lt;/id&gt;
    &lt;column1&gt;test&lt;/column1&gt;
    &lt;column2&gt;62&lt;/column2&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p>Whilst for updates it is:</p>
  
  <pre><code>&lt;row&gt;
    &lt;id&gt;1&lt;/id&gt;
    &lt;column1&gt;test&lt;/column1&gt;
    &lt;column2&gt;62&lt;/column2&gt;
  &lt;/row&gt;
  &lt;row&gt;
    &lt;id&gt;1&lt;/id&gt;
    &lt;column1&gt;changed&lt;/column1&gt;
    &lt;column2&gt;62&lt;/column2&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p>Is it possible to transform the xml so that it would like this:</p>
  
  <pre><code>--Inserts
  &lt;row&gt;
    &lt;id old="" new="1"&gt;&lt;/id&gt;
    &lt;column1 old="" new="test"&gt;&lt;/column1&gt;
    &lt;column2 old="" new="62"&gt;&lt;/column2&gt;
  &lt;/row&gt;
  
  --Deletes
  &lt;row&gt;
    &lt;id old="1" new=""&gt;&lt;/id&gt;
    &lt;column1 old="changed" new=""&gt;&lt;/column1&gt;
    &lt;column2 old="62" new=""&gt;&lt;/column2&gt;
  &lt;/row&gt;
  
  --Updates
  &lt;row&gt;
    &lt;id old="1" new="1"&gt;&lt;/id&gt;
    &lt;column1 old="test" new="changed"&gt;&lt;/column1&gt;
    &lt;column2 old="62" new="62"&gt;&lt;/column2&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p>Better still, for updates, is it possible to leave out unchanged columns so that the xml would be just:</p>
  
  <pre><code>&lt;row&gt;
    &lt;column1 old="test" new="changed"&gt;&lt;/column1&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I managed to come up with this SQL:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>However, for updates I also have the unchanged columns. Is it possible to filter out the unchanged columns by improving on the above SQL?</p>