Posted by markc600 on 05/17/06 15:06

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

• Next in forum: Re: Timeout errors
• Prev in forum: Re: create table with recursive relationship
• Thread view: Re: Putting attribute on root node using FOR XML PATH

[Reply to this message]