Structuring xml returned by select statement in Sql Server

Question

Lets say, I have two tables

ItemGoods, ServiceGoods which have Name and Price among other columns.

representing the different kinds of goods sold.

I want to select it into a single xml structure.

Right now I am using this select

DECLARE @goods_prices VARCHAR(8000)
SET @goods_prices = (select * from
                        (SELECT  [name] item_name,
                                 [cost] price   
                              FROM  ItemGoods
                        UNION
                        SELECT  [name] service_name,
                                [cost] price
                              FROM  ServiceGoods)   goods
                    FOR XML AUTO);

Output I get :

<goods>
  <itemName>Item1</itemName>
  <price>299.0</price>
</goods>
<goods>
  <itemName>Service1</itemName>
  <price>4,99</price>
</goods>

The output I seek would be something like

<goods>
  <itemGoods>
    <item>
      <itemName>Item1</itemName>
      <price>299.0</price>
    </item>
  </itemGoods>
  <serviceGoods>
    ...
  </serviceGoods>
</goods>

How do I achieve this? I need to pack the result into a single variable. A more simplistic xml structure would do, just as long as I can define items from services

Answer 1

You can use this (I'm using variable table for convenience but it works also with 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 INTO @ItemGoods VALUES('Item1', 299.0)
INSERT INTO @ServiceGoods VALUES('Service1', 4.99)

SELECT (
  SELECT NAME AS itemName
   , COST AS price
  FROM @ItemGoods
  FOR XML PATH('Item'), TYPE
) AS ItemGoods
, (
  SELECT NAME AS itemName
   , COST AS price
  FROM @ServiceGoods
  FOR XML PATH('Service'), TYPE
) AS ServiceGoods
FOR XML PATH('Goods')

And the 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>