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

Julien Vavasseur · Accepted Answer · 2015-10-22 14:57:56Z

It works fine with FOR XML EXPLICIT (Use EXPLICIT Mode with FOR XML

declare @ItemGoods table(name varchar(50), cost varchar(50))
declare @ServiceGoods table(name varchar(50), cost varchar(50))

Insert into @ItemGoods(name, cost) values('Item1', '299'), ('Item2', '333')
Insert into @ServiceGoods(name, cost) values('Serv1', '555'),('Serv2', '222')



Select 1 as Tag, NULL as parent
    , NULL as [goods!1!]
    , NULL as [itemGoods!2!]
    , NULL as [item!3!itemName!Element]
    , NULL as [item!3!price!Element]
    , NULL as [serviceGoods!4!]
    , NULL as [item!5!itemName!Element]
    , NULL as [item!5!price!Element]
Union All
Select 2 as Tag, 1 as parent
    , NULL
    , NULL, NULL, NULL
    , NULL, NULL, NULL
Union All
Select 3 as Tag, 2 as parent
    , NULL
    , NULL, [name], [cost]
    , NULL, NULL, NULL
FROM  @ItemGoods item
Union All 
Select 4 as Tag, 1 as parent
    , NULL
    , NULL, NULL, NULL
    , NULL, NULL, NULL
Union All
Select 5 as Tag, 4 as parent
    , NULL
    , NULL, NULL, NULL
    , NULL, [name], [cost]
FROM  @ServiceGoods item
For xml explicit

Output:

<goods>
  <itemGoods>
    <item>
      <itemName>Item1</itemName>
      <price>299</price>
    </item>
    <item>
      <itemName>Item2</itemName>
      <price>333</price>
    </item>
  </itemGoods>
  <serviceGoods>
    <item>
      <itemName>Serv1</itemName>
      <price>555</price>
    </item>
    <item>
      <itemName>Serv2</itemName>
      <price>222</price>
    </item>
  </serviceGoods>
</goods>

Aka Guymelef · Accepted Answer · 2015-10-22 14:58:12Z

0

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>

answered Oct 22, 2015 at 14:58

Aka Guymelef

1115 bronze badges

1 Comment

evictednoise Over a year ago

Magnificent. Thank you kindly. I tried nesting it but I was missing that outer select ( ) itemGoods, and SQL Server would throw error.

Collectives™ on Stack Overflow

Structuring xml returned by select statement in Sql Server

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related