How can I add an attribute to the root element of xml generated by SQL's Select for xml

Question

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:

Id (PK, int, not null) Action (nchar(1), not null) ActionDate (datetime, not null) ActionUser (nvarchar(100), not null) AuditData (XML(.), not null)

In my trigger, I'm doing something like the following:

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

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:

<Root tableName = "Person">
    <Id>132</Id>
    <FirstName>Ryan</FirstName>
    ...
</Root>

Is there any way to accomplish this with the select from ... for xml statement?

Answer 1

Taking help from this thread, I was able to come up with this and it seems to work:

select 'Person' as "@tableName",
 (select * from deleted for xml path('DataItem'), type)
 for xml path('Root')