For Xml Auto Help

In sql 2005 if I have a query along the lines of:
select * from myTable
for xml auto, elements xsinil, root('myRoot')
I will get back one row, one column, filled with a nice xml string.
The problem I'm having is figuring out how to access that data, such that I
can put it into a local var. What I want to do looks something like this:
declare @.foo varchar(max)
select @.foo = * from myTable
for xml auto, elements xsinil, root('myRoot')
select @.foo
This brings about an error of course, even though the final result is just
one row and column of data.
Does anybody know how I can get the results of a query using "for xml auto"
into a local variable?
Thanks,
KevinJust use an xml type variable e.g.
declare @.x xml
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'))
select @.x as 'XML Result'
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>|||Just to add a tiny bit:
The TYPE directive mean that the result from the query is of the datatype XM
L instead of a string.
It doesn't make any difference in Jasper's example, as there would be an imp
licit datatype
conversation from string to xml anyhow, but it might be useful in other case
s. The TYPE directive is
obviously new for 2005 (as the xml datatype is).
set @.x = (select [name]
from sys.databases as [database]
for xml auto,root('databases'), type)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:u9$Y3ODDGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Just use an xml type variable e.g.
> declare @.x xml
> set @.x = (select [name]
> from sys.databases as [database]
> for xml auto,root('databases'))
> select @.x as 'XML Result'
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
> news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Jasper and Tibor, that's just what I needed.
Kevin
"Kevin Thomas" <SendSpamHere@.Spam.com> wrote in message
news:ONVTYBCDGHA.2820@.TK2MSFTNGP11.phx.gbl...
> In sql 2005 if I have a query along the lines of:
> select * from myTable
> for xml auto, elements xsinil, root('myRoot')
> I will get back one row, one column, filled with a nice xml string.
> The problem I'm having is figuring out how to access that data, such that
> I can put it into a local var. What I want to do looks something like
> this:
> declare @.foo varchar(max)
> select @.foo = * from myTable
> for xml auto, elements xsinil, root('myRoot')
> select @.foo
> This brings about an error of course, even though the final result is just
> one row and column of data.
> Does anybody know how I can get the results of a query using "for xml
> auto" into a local variable?
> Thanks,
> Kevin
>
>