Posted by chippy on 05/16/06 23:09

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

• Next in forum: Re: T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures no other procedure with the same name
• Prev in forum: Re: Create Status Table
• Thread view: Putting attribute on root node using FOR XML PATH

[Reply to this message]