for xml to local variable

Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over the * when this technically meets the requirements for setting a local variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect inside an SQL stored Proc.?
WR
Unfortunately, this can't be done in SQL Server 2000 - what actually gets
returned is a single column/single row resultset containing the XML stream.
The client-side components of SQLXML can extract that as a stream but
there's no way to do it in T-SQL.
In SQL Server 2005, you can use the xml data type to do what you're
suggesting.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"WildRide" <WildRide@.discussions.microsoft.com> wrote in message
news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
Is there a reason why the following does not work...
declare @.s varchar(1024)
set @.s = (select * from validTable for xml auto)
...assuming I know for a fact that the returned xml stream will fit inside
my declared variable.
I suspect it's just the pre-compiler getting it's knickers in a knot over
the * when this technically meets the requirements for setting a local
variable.
... i think...
Does any one have any comments or ideas on how to achieve the same effect
inside an SQL stored Proc.?
WR
|||sorry but i have testing this code with sqlserver 2000
and it doesent work
Cdlt
Query:
declare @.s varchar(1024)
set @.s = (select * from USERPROFILE for xml auto)
Result:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'xml'.

>--Original Message--
>Unfortunately, this can't be done in SQL Server 2000 -
what actually gets
>returned is a single column/single row resultset
containing the XML stream.
>The client-side components of SQLXML can extract that as
a stream but
>there's no way to do it in T-SQL.
>In SQL Server 2005, you can use the xml data type to do
what you're
>suggesting.
>Cheers,
>Graeme
>--
>--
>Graeme Malcolm
>Principal Technologist
>Content Master Ltd.
>www.contentmaster.com
>www.microsoft.com/mspress/books/6137.asp
>
>"WildRide" <WildRide@.discussions.microsoft.com> wrote in
message
>news:06CEF1F3-F947-45B9-93BE-BE8FEF4E3DF6@.microsoft.com...
>Is there a reason why the following does not work...
>declare @.s varchar(1024)
>set @.s = (select * from validTable for xml auto)
>...assuming I know for a fact that the returned xml
stream will fit inside
>my declared variable.
>I suspect it's just the pre-compiler getting it's
knickers in a knot over
>the * when this technically meets the requirements for
setting a local
>variable.
>... i think...
>Does any one have any comments or ideas on how to achieve
the same effect
>inside an SQL stored Proc.?
>WR
>
>.
>
|||"Boss Hog" <anonymous@.discussions.microsoft.com> wrote in message
news:74c001c4764f$9c4d6bb0$a301280a@.phx.gbl...
> sorry but i have testing this code with sqlserver 2000
> and it doesent work
It won't work because it isn't supported.
Bryant