Here is the query:
 DECLARE @.MessageBody XML
 DECLARE @.AuditTable SYSNAME
 DECLARE @.SendTrans BIT
 DECLARE @.SendAudit BIT
 DECLARE @.RecordCount INT
 DECLARE @.OperationType CHAR(1)
 SET @.RecordCount = @.@.ROWCOUNT
 SET @.OperationType = 'U'
 SET @.SendTrans = 1 
 SET @.SendAudit = 1
 SET @.AuditTable = 'States'
 SELECT @.MessageBody = (
 SELECT * FROM
 (
 SELECT TOP 10
 'INSERTED' AS ActionType, @.SendTrans AS SendTrans, @.SendAudit AS SendAudit, 
 COLUMNS_UPDATED() AS ColumnsUpdated, GETDATE() AS AuditDate, 
 @.AuditTable AS AuditTable, 'test' AS UserName, @.RecordCount AS RecordCount, *
 FROM l_states 
 )AuditRecord
 FOR XML AUTO, ROOT('AuditTable'), BINARY BASE64)
SELECT @.MessageBody
In my DEV env (Developer Edition), this result is produced:
<AuditTable>
 <AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AK" State="Alaska" />
 <AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:43:12.497" AuditTable="States" UserName="test" RecordCount="1" StateAbbreviation="AL" State="Alabama" />
</AuditTable>
In my Enterprise Edition evn, this is the result:
<AuditTable>
 <AuditRecord ActionType="INSERTED" SendTrans="1" SendAudit="1" AuditDate="2007-06-22T15:44:48.230" AuditTable="States" UserName="test" RecordCount="1">
 <l_states StateAbbreviation="AK" State="Alaska" />
 <l_states StateAbbreviation="AL" State="Alabama" />
 </AuditRecord>
</AuditTable>
Does anyone have any idea what might be wrong? Any help is greatly appreciated.
TimYou might want to check the compat leve of two databases. The DEV behavior should level=90 (SQL 2005) behavior - using derived table name as row element name. The Ent is SQL 2000 behavior (level=80).
 
No comments:
Post a Comment