Minor incompatibility between 2000 and 2005

Hi All!
Provided that the following works in both 2000 and 2005 (it does):
SELECT * FROM t WHERE id = 123 FOR XML AUTO
...I found that the following works in 2005 but doesn't work in 2000:
DECLARE @.result AS XML
SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
The error message is:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
Is there a workaround to make the syntax above work in SQL Server 2000
or I'm not doing it right?
Thank you!
Sergey.
Versions:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows NT 5.2 (Build 3790: Service Pack 1) (a.k.a. SQL Server 2000
Service Pack 4)
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005
00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition
on Windows NT 5.1 (Build 2600: Service Pack 2)Hello Sergey Lipnevich sergey.at.optimaltec.com,

> ...I found that the following works in 2005 but doesn't work in 2000:
> DECLARE @.result AS XML
> SET @.result = (SELECT * FROM t WHERE id = 123 FOR XML AUTO)
> The error message is:
No, for two reasons:
a. SS2000 doesn't have the XML datatype
b. In SS2000, XML serialization is done post query, so you can't assign the
result of XML operation to a variable. In 2005, XML operations are done in
query, so you can.
These are both known features. No, really. Features. :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent Tegels wrote:
> a. SS2000 doesn't have the XML datatype
"DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
supports this type. I guess I was wrong ;-).

> b. In SS2000, XML serialization is done post query, so you can't assign
> the result of XML operation to a variable. In 2005, XML operations are
> done in query, so you can.
This explains it I think. Thanks!

> These are both known features. No, really. Features. :)
Known... Yes. Features... I don't know :-).
Sergey.|||It was by design as an implementation restriction in SQL Server 2000 that
you could not assign the result.
Best regards
Michael
PS: And no, we did not do it just so you have to buy SQL Server 2005 to get
it ... ;)
"Sergey Lipnevich" <sergey.at.optimaltec.com> wrote in message
news:uyj2rSqTGHA.5332@.tk2msftngp13.phx.gbl...
> Kent Tegels wrote:
> "DECLARE @.result AS XML" doesn't fail, which led me to believe 2000
> supports this type. I guess I was wrong ;-).
>
> This explains it I think. Thanks!
>
> Known... Yes. Features... I don't know :-).
> Sergey.|||Michael Rys [MSFT] wrote:
> It was by design as an implementation restriction in SQL Server 2000 that
> you could not assign the result.
Michael,
Thanks for replying! I don't expect to get a solution so easily ;-), but
maybe you can hint at where to look for. I need to do something like
this (parent has 1:1 relationships with its two child tables):
CREATE FUNCTION snapshot (@.id BIGINT)
RETURNS XML
AS BEGIN
RETURN (
SELECT
parent.*,
CASE parent.type
WHEN 1 THEN (
SELECT child_one.*
FROM child_one
WHERE child_one.id = parent.id
FOR XML AUTO
)
WHEN 2 THEN (
SELECT child_two.*
FROM child_two
WHERE child_two.id = parent.id
FOR XML AUTO
)
ELSE NULL
END AS details
FROM parent
WHERE parent.id = @.id
FOR XML AUTO
)
END
This works great in 2005, but how can I restructure the function to work
in both 2000 (proper/latest SQLXML and SP versions not a problem) and
2005? Thank you!
Sergey.