August 11, 2008 at 1:25 pm
I have two apparently identical databases on the same server instance and when I use a select for xml auto statement I get the results displayed differently!
Table UserTable
UserName varchar(255) not null,
UserActive bit not null default(1)
The query I am running is:
SELECT * FROM (
SELECT UserName as a,
UserActive as b
FROM UserTable
) AS Results FOR XML AUTO, ELEMENTS
(For information I have replaced the less than and greater than xml tags in the next section with {} as it wouldn't display in this forum if I left them in.)
One database returns the dataset as
{Results}
{a}User1{/a}
{b}1{/b}
{/Results}
{Results}
{a}User2{/a}
{b}1{/b}
{/Results}
and the other database returns
{UserTable}
{a}User1{/a}
{b}1{/b}
{/UserTable}
{UserTable}
{a}User2{/a}
{b}1{/b}
{/UserTable}
Does anyone know why the second set is ignoring the "Results" part and what we can do to resolve this? Our application is not working because it's not getting the data in the correct format.
Any help would be greatly appreciated.
Regards,
August 11, 2008 at 1:34 pm
Note: Here are the result sets with proper xml tags in them
One database returns the dataset as
<Results>
<a>User1</a>
<b>1</b>
</Results>
<Results>
<a>User2</a>
<b>1</b>
</Results>
and the other database returns
<UserTable>
<a>User1</a>
<b>1</b>
</UserTable>
<UserTable>
<a>User2</a>
<b>1</b>
</UserTable>
August 11, 2008 at 1:48 pm
Found it.
If you have a database on '05 that's running in 2K mode it won't return the resultset correctly. Change the compatability level to 2005 and it works, although you may need to wait for the memory cache to clear.
It's 8:45pm, I have the result I need so now I am going to the pub.
G'night! 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply