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

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.