select for xml mysql_sql-server – 如何向SQL的Select for xml生成的xml的根元素添加一个属性...

我的数据库中有很多表.这些表中的每一个都有触发器在更新或删除时触发,以将更改记录到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看起来像：

132

Ryan

...

有没有办法通过select for … for xml语句来实现这一点？