x

How to generate empty XML element for NULL or non matching values

I have the following query which generates an XML

use tempdb
go

-- Prepare sample data
DECLARE  @Listing  TABLE    (
     ListingID INT,
     Name VARCHAR(200)
    )

DECLARE @Category TABLE (
    ListingID INT,
    CategoryID INT,
    Name varchar(200)
)

INSERT      @Listing
VALUES (1, 'Nike'), (2, 'Pizza hut'),(3, 'Towing Dad'),(4, 'Glorious ManiPedi')


INSERT @Category
values (1,1,'Apparel'),(2,2, 'Pizzeria'),(2,3,'All Restaurants'),(3,4,'Towing')

-- Display the XML
SELECT     
     s.ListingID as [@ListingID],
     s.ListingName as [@ListingName],
    (
        SELECT      x.CategoryID AS [CategoryId],
                    x.Name AS [CategoryName]
        FROM        @Category AS x
        WHERE       x.ListingID = s.ListingID
        FOR XML     PATH('Categories'),
                    TYPE 
     )
FROM  (
        SELECT      ListingID, Name as ListingName
        FROM        @Listing 
        GROUP BY    ListingID, Name
      ) AS s
FOR XML     PATH('Listing'),
            ROOT('Listings');

It generates the following XML:

<Listings>
  <Listing ListingID="1" ListingName="Nike">
    <Categories>
      <CategoryId>1</CategoryId>
      <CategoryName>Apparel</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="2" ListingName="Pizza hut">
    <Categories>
      <CategoryId>2</CategoryId>
      <CategoryName>Pizzeria</CategoryName>
    </Categories>
    <Categories>
      <CategoryId>3</CategoryId>
      <CategoryName>All Restaurants</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="3" ListingName="Towing Dad">
    <Categories>
      <CategoryId>4</CategoryId>
      <CategoryName>Towing</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="4" ListingName="Glorious ManiPedi" />
</Listings>

How can I make the above query generate the following XML:

<Listings>
  <Listing ListingID="1" ListingName="Nike">
    <Categories>
      <CategoryId>1</CategoryId>
      <CategoryName>Apparel</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="2" ListingName="Pizza hut">
    <Categories>
      <CategoryId>2</CategoryId>
      <CategoryName>Pizzeria</CategoryName>
    </Categories>
    <Categories>
      <CategoryId>3</CategoryId>
      <CategoryName>All Restaurants</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="3" ListingName="Towing Dad">
    <Categories>
      <CategoryId>4</CategoryId>
      <CategoryName>Towing</CategoryName>
    </Categories>
  </Listing>
  <Listing ListingID="4" ListingName="Glorious ManiPedi">
       <Categories>
           <CategoryId />
           <CategoryName />
       </Categories>
  </Listing>
</Listings
Any tips/pointers is appreciated, thanks
more ▼

asked Jun 15, 2012 at 11:15 AM in Default

bonskijr gravatar image

bonskijr
204 5 5 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

bit of a hack.....but this checks to see if the inner select returns null, and replaces it with '' instead..

SELECT     
     s.ListingID as [@ListingID],
     s.Name as [@ListingName],
    isnull(
    (
        SELECT      x.CategoryID AS [CategoryId],
                    x.Name AS [CategoryName]
        FROM        @Category AS x 
       where      x.ListingID = s.ListingID 
        FOR XML     PATH('Categories'),
                    TYPE 
     ),
     (select  '' AS [CategoryId], '' AS [CategoryName]
      FOR XML     PATH('Categories'),
                    type
     ) 
     )               
FROM  @Listing AS s
FOR XML     PATH('Listing'),
            ROOT('Listings');
I'm sure there's a better solution with a simple join ....
more ▼

answered Jun 15, 2012 at 11:37 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Nice, but yeah I think this will have to do... thanks @kev
Jun 15, 2012 at 12:20 PM bonskijr
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x150

asked: Jun 15, 2012 at 11:15 AM

Seen: 1314 times

Last Updated: Jun 15, 2012 at 12:20 PM