Structuring xml returned by select statement in Sql Server -

lets say, have 2 tables

itemgoods, servicegoods have name , price among other columns.

representing different kinds of goods sold.

i want select single xml structure.

right using select

declare @goods_prices varchar(8000) set @goods_prices = (select *                         (select  [name] item_name,                                  [cost] price                                   itemgoods                         union                         select  [name] service_name,                                 [cost] price                                servicegoods)   goods                     xml auto); 

output :

<goods>   <itemname>item1</itemname>   <price>299.0</price> </goods> <goods>   <itemname>service1</itemname>   <price>4,99</price> </goods> 

the output seek like

<goods>   <itemgoods>     <item>       <itemname>item1</itemname>       <price>299.0</price>     </item>   </itemgoods>   <servicegoods>     ...   </servicegoods> </goods> 

how achieve this? need pack result single variable. more simplistic xml structure do, long can define items services

you can use (i'm using variable table convenience works real tables obviously) :

declare @itemgoods table (   name nvarchar(50) not null,   cost decimal(18, 2) not null )  declare @servicegoods table (   name nvarchar(50) not null,   cost decimal(18, 2) not null )  insert @itemgoods values('item1', 299.0) insert @servicegoods values('service1', 4.99)  select (   select name itemname    , cost price   @itemgoods   xml path('item'), type ) itemgoods , (   select name itemname    , cost price   @servicegoods   xml path('service'), type ) servicegoods xml path('goods') 

and output:

<goods>   <itemgoods>     <item>       <itemname>item1</itemname>       <price>299.00</price>     </item>   </itemgoods>   <servicegoods>     <service>       <itemname>service1</itemname>       <price>4.99</price>     </service>   </servicegoods> </goods>