Putting attribute on root node using FOR XML PATH

I would like to know how to put an attribute on the root node of the
xml returned from a FOR XML PATH query. One thing I tried is this:

select
m.msgid '@.msgID',
st.namelong 'set/@.namelong',
st.nameshort 'set/@.nameshort',
from
msgset m
inner join settable st on (st.setid = m.setid)
where m.msgID = 195
for xml path('set'), root('message')

but it gives me:

<message>
<set msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</set>
<set msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</set>
<set msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</set>
</message
here is what I want:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
<set namelong="MORE STUFF" nameshort="M STUFF" />
<set namelong="TESTING 123" nameshort="TEST" />
</message
I can't get it. If I use: root(''), then it tells me: "Row tag
omission (empty row tag name) cannot be used with attribute-centric FOR
XML serialization." I'm sure there is a trick to this-- any
suggestions?

Many thanks.
the chippsterTry using a nested query instead

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from settable st
where st.setid = m.setid
for xml path('set'),type)
from
msgset m
where m.msgID = 195
for xml path('message'), type|||Thank you for your suggestion. However, running your query gives me
this:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</message>
<message msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</message>
<message msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</message
Thanks|||Can you post your DDL and sample data.

Here what I used based on your narrative which
gives the results you wanted

declare @.settable table(msgid int,namelong varchar(20),nameshort
varchar(10),setid int)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'STUFF
HERE','STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'MORE
HERE','M STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'TESTING
123','TEST',1)
declare @.msgset table(msgid int,setid int)
insert into @.msgset(msgid,setid) values(195,1)

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from @.settable st
where st.setid = m.setid
for xml path('set'),type)
from
@.msgset m
where m.msgID = 195
for xml path('message'), type|||Thanks again for your reply. I think the problem here is that the
relationship between the msgset table and the settable is many-to-many.
So, the sample data would look like this:

declare @.settable table(msgid int,namelong varchar(20),nameshort
varchar(10),setid int)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'STUFF
HERE','STUFF',1)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'MORE
HERE','M STUFF',2)
insert @.settable(msgid,namelong,nameshort,setid) values(195,'TESTING
123','TEST',3)
declare @.msgset table(msgid int,setid int)
insert into @.msgset(msgid,setid) values(195,1)
insert into @.msgset(msgid,setid) values(195,2)
insert into @.msgset(msgid,setid) values(195,3)

This is an important detail, I'm sorry I left it out.

Any help would be much appreciated-- it seems like there should be a
simple solution to this.
Thanks.|||OK, I figured out a solution:

select
m.msgid '@.msgID',
(
select st.namelong '@.namelong',
st.nameshort '@.nameshort'
from settable st, msgset ms
where st.setid = ms.setid
and ms.msgid = 195
for xml path('set'),type)
from
message m
where m.msgID = 195
for xml path('message'), type

I joined another table that created a one-to-many against the msgset
table. That seems to keep the root node down to one. Thanks for
pointing me in the right direction.

--chip