By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Putting attribute on root node using FOR XML PATH

P: n/a
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 chippster

May 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

Try 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

May 17 '06 #2

P: n/a
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

May 17 '06 #3

P: n/a

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

May 17 '06 #4

P: n/a
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.

May 17 '06 #5

P: n/a
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

May 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.