"FOR XML EXPLICIT" query Works on SQL 2000 but same does not work on 2005 issues
-
We are using a stored procedure which uses FOR XML EXPLICIT and it works fine with SQL Server 2000 but doesnt work with SQL Server 2005. Can anyone help me out in understanding the reason behind such a behavior and any possible solution. Please find the details of the problem below:
The procedure runs fine in SQL 2000 the input xml and gives us the correct XML:
SELECT
1 AS TAG,
NULL AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
NULL AS [user!2!title!element],
NULL AS [user!2!firstname!xml],
NULL AS [user!2!lastname!xml],
NULL AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
tUser.title_lookup_id AS [user!2!title!element],
tUser.firstname AS [user!2!firstname!xml],
tUser.lastname AS [user!2!lastname!xml],
tUser.login_name AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
order by [mailbox!1!mailbox-name!element], [user!2!userID!element]
FOR XML EXPLICIT
This runs perfectly fine in 2000 but in the 2005 the XML is not correctly formed
.. i am not able to figure out why this issue is occuring. In 2005 tt gives me all the tags with the expected values but the sequence is not correct.Thanks,
Gaurav
-
Change tUser.id to NULL.
SELECT
1 AS TAG,
NULL AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
NULL AS [user!2!title!element],
NULL AS [user!2!firstname!xml],
NULL AS [user!2!lastname!xml],
NULL AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
tUser.title_lookup_id AS [user!2!title!element],
tUser.firstname AS [user!2!firstname!xml],
tUser.lastname AS [user!2!lastname!xml],
tUser.login_name AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
order by [mailbox!1!mailbox-name!element], [user!2!userID!element]
FOR XML EXPLICIT
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply