我的数据库中有很多表.这些表中的每一个都有触发器在更新或删除时触发,以将更改记录到AuditLog表. AuditLog表包含以下内容：
Id(PK,int,not null)
Action(nchar(1),not null)
ActionDate(datetime,not null)
ActionUser(nvarchar(100),not null)
AuditData(XML(.),not null)
在我的触发器中,我正在做如下事情：
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
这很好,但是,我想做的是向tablename的根元素添加一个属性,以便AuditData xml看起来像：
<Root tableName = "Person">
<Id>132</Id>
<FirstName>Ryan</FirstName>
...
</Root>
有没有办法通过select for … for xml语句来实现这一点？
解决方法
从
this线程的帮助下,我能够想出这一点,似乎有效：
select 'Person' as "@tableName",(select * from deleted for xml path('DataItem'),type)
for xml path('Root')
