For Xml Auto Help

29 Dec 2005 2:34 AM

Kevin Thomas

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

29 Dec 2005 4:52 AM

Jasper Smith

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

Show quote

"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
>
>
>

29 Dec 2005 9:12 AM

Tibor Karaszi

Just to add a tiny bit:

The TYPE directive mean that the result from the query is of the datatype XML instead of a string.
It doesn't make any difference in Jasper's example, as there would be an implicit datatype
conversation from string to xml anyhow, but it might be useful in other cases. 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/

Show quote

"Jasper Smith" <jasper_smi***@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...
>> 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
>>
>>
>>
>
>

29 Dec 2005 6:49 PM

Kevin Thomas

Thanks Jasper and Tibor, that's just what I needed.

Kevin

Show quote

"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
>
>
>