title: How can I add an attribute to the root element of xml generated by SQL's Select for xml
body: <p>I've got a number of tables in my database.  Each of these tables has a trigger that fires on update or delete to log changes to an AuditLog table.  The AuditLog table contains the following:</p>
  
  <p>Id (PK, int, not null)
  Action (nchar(1), not null)
  ActionDate (datetime, not null)
  ActionUser (nvarchar(100), not null)
  AuditData (XML(.), not null)</p>
  
  <p>In my trigger, I'm doing something like the following:</p>
  
  <pre><code>DECLARE @auditBody XML
  SET @auditBody = (select * from deleted as Root for xml auto, elements)
  insert into dbo.AuditLog 
     (Action, ActionDate, ActionUser, AuditData)
     select Case
          When I.Id is not null then 'U'
          Else 'D'
         End as Action
       ,getdate() as ActionDate
       ,suser_name() as ActionUser
       ,@auditBody as AuditData
    From
      deleted D Left Join
      inserted I on D.Id = I.Id
  </code></pre>
  
  <p>This works great, however, what I'd like to do is add an attribute to the Root element of tablename so that the AuditData xml would look something like:</p>
  
  <pre><code>&lt;Root tableName = "Person"&gt;
      &lt;Id&gt;132&lt;/Id&gt;
      &lt;FirstName&gt;Ryan&lt;/FirstName&gt;
      ...
  &lt;/Root&gt;
  </code></pre>
  
  <p>Is there any way to accomplish this with the select from ... for xml statement?</p>