FOR XML works differently between Developer and Enterprise Edition in SQL 2005

If I run the same FOR XML query in a Development edition enviornment and a Enterprise Edition environment, the results are different. The query is exactly the same.

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.
Tim

Tim,

What is the results of @.@.VERSION on both servers? (I suspect a difference in versions...)

For XML works different for different sql server 2005 editions

If I run the same FOR XML query in a Development edition enviornment and a Enterprise Edition environment, the results are different. The query is exactly the same.

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).