For XML Explicit

k. I'm going crazy here. I have some for xml explicit sql working (created a
test against the adventureworks database) but how can I get the results of
this sql into an xml variable. I have tried inserting it into a table
variable or setting an xml variable equal to it...I can't get it to
working...any help given is appreciated.
Select 1 as Tag
,null as Parent
,null as [Audit!1]
,'Test' as [Audit!1!type]
,null as [TestText!2]
,null as [Contacts!3]
,null as [ContectId!4]
Union All
Select 2
,1
,null
,null
,'This is test text'
,null
,null
Union All
Select 3
,1
,null
,null
,null
,null
,null
Union All
Select 4
,3
,null
,null
,null
,null
,ContactId
from Person.Contact
where contactId between 1 and 10
For XML ExplicitHello JI,
J> k. I'm going crazy here. I have some for xml explicit sql working
J> (created a test against the adventureworks database) but how can I
J> get the results of this sql into an xml variable. I have tried
J> inserting it into a table variable or setting an xml variable equal
J> to it...I can't get it to working...any help given is appreciated.
SQL 2000 or 2005? In 2005 this should work:
declare @.x xml;set @.x = (select * from (select 1 as Tag,null as Parent,null
as [Audit!1],'Test' as [Audit!1!type],null as [TestText!2],null as [Contacts
!3],null
as [ContectId!4] Union All Select 2,1,null,null,'This is test text',null,nul
l
Union All Select 3,1,null,null,null,null,null Union All Select 4,3,null,null
,null,null,ContactId
from Person.Contact where contactId between 1 and 10) as c for xml explicit,
type);select
@.x;
In 2000, XML aggreation of the UT occurs post query, so you couldn't do that
there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is 2k5.
Thank you Kent!
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741ea2a8c8432165f54180@.news.microsoft.com...
> Hello JI,
> J> k. I'm going crazy here. I have some for xml explicit sql working
> J> (created a test against the adventureworks database) but how can I
> J> get the results of this sql into an xml variable. I have tried
> J> inserting it into a table variable or setting an xml variable equal
> J> to it...I can't get it to working...any help given is appreciated.
> SQL 2000 or 2005? In 2005 this should work:
> declare @.x xml;set @.x = (select * from (select 1 as Tag,null as
> Parent,null as [Audit!1],'Test' as [Audit!1!type],null as
> [TestText!2],null as [Contacts!3],null as [ContectId!4] Union All Select
> 2,1,null,null,'This is test text',null,null Union All Select
> 3,1,null,null,null,null,null Union All Select
> 4,3,null,null,null,null,ContactId from Person.Contact where contactId
> between 1 and 10) as c for xml explicit,type);select @.x;
> In 2000, XML aggreation of the UT occurs post query, so you couldn't do
> that there.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>