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

Pulling data across multiple tables.

I am attempting to pull all users over the age of 30 from a table. I want the results to be returned off of their membership status and recent activity. Their membership status is stored in a seperate table (Payment). Some users have more than one membership status record, while others might not have any at all.

LEFT JOIN seems like a possible solution, however, if a user has more than one membership status record, it is returning multiple results for that user (when I only want 1 result for that user).

Here is the code that I have so far.

SELECT Users.UserID FROM Users LEFT JOIN Payment ON Users.UserID = Payment.UserID AND Users.Age > 30 AND Payment.PaymentExpirationDate > '3/28/2004' ORDER BY Payment.MembershipNumber DESC, Users.LastActive DESC

Payment.MembershipNumber - INT Field. This is the membership status for the user, if the user has any membership records.
Users.LastActive - Date/Time Field. This is the recent active for the user.

The data is being returned right now is:
UserID
=====
3
8
12
12
12
9
9
1
... and so on, when it should look like ...
UserID
=====
3
8
12
9
1

I hope this makes sense. Thanks for your time.

Jamesif a user has more than one membership status record, which one do you want?|||I need the first membership status record that has a PaymentExpirationDate closest to '3/28/2004'.|||select U.UserID
, U.lastActive
, P.PaymentExpirationDate
from Users U
left
join Payment P
on U.UserID
= P.UserID
and P.PaymentExpirationDate
> '3/28/2004'
where U.Age > 30
and P.PaymentExpirationDate
= ( select min(PaymentExpirationDate)
from Payment
where UserID = P.UserID
and PaymentExpirationDate
> '3/28/2004' )
order
by U.LastActive desc