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