SQL Server 2005 FOR XML Clause

Question

We are having an issue where we are running the exact same query on two different servers, running the exact same service pack and update - 9.0.4211 - but receiving two differently formatted XML resultsets. Does anyone have any idea why/how this is happening? Is there a setting/schema involved somehow in the way SQL Server generates the resulting XML format? I understand that we can transform the xml with the PATH option but at this point all of the client code is written to use the format from one of the result sets and using the path options still does not get us what the AUTO option does for the correct format.

Query -

SELECT distinct 

operation.operationid,
@zoneId as ZoneID,
member.name, IsNull(member.firstname, '') as firstname, IsNull(member.lastname, '') as lastname, IsNull(member.email, '') as email, IsNull(member.memberid, '') as memberid, IsNull(member.type, '') as type, IsNull(member.AppMemberID, '') as AppMemberID
FROM IQSECURE_TaskOperations operation 
join IQSECURE_RoleTasks r on operation.taskid = r.taskid
join IQOBASE_Rosters roster on r.roleid = roster.roleid
left outer join IQObase_Members member on roster.rosterid = member.Rosterid 
WHERE operation.operationid = @operationid AND roster.zoneid = @zoneId and member.memberid is not null


union

SELECT distinct 
operation.operationid,
@zoneID as ZoneID,
member.name, IsNull(member.firstname, '') as firstname, IsNull(member.lastname, '') as lastname, IsNull(member.email, '') as email, IsNull(member.memberid, '') as memberid, IsNull(member.type, '') as type, IsNull(member.AppMemberID, '') as AppMemberID
FROM IQSECURE_TaskOperations operation 
join IQSECURE_RoleTasks r on operation.taskid = r.taskid
join IQOBASE_Rosters roster on r.roleid = roster.roleid
left outer join IQOBASE_Members_Exploded member on roster.rosterid = member.Rosterid 
WHERE operation.operationid = @operationid AND roster.zoneid = @zoneid and member.memberid is not null

ORDER BY IsNull(member.type, ''), IsNull(member.lastname, ''), IsNull(member.firstname, ''), member.name

FOR XML AUTO, XMLDATA

Returned xml can be provided if need be.

Thanks in advance for any help provided.

Jon

Answer 1

This problem was a two part problem. The two databases were not exactly the same, one had SQL 2000 compatibility mode the other SQL 2005. The one with SQL 2000 was generating the correct XML format. So, one fix was to make the other SQL 2000 compatible. The other solution was to rewrite the select without the UNION. For some reason the UNION caused the incorrect XML format.

Answer 2

You can check if there are any differences on the database:

SELECT * 
FROM sys.configurations
ORDER BY name ;

And I would also check the client you are using. If you are using SQL Management Studio there is a menu option Tools / Options. This has setting that might affect what you are seeing.