x
login about faq Site discussion (meta-askssc)

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 '12 at 11:15 AM in Default

bonskijr gravatar image

bonskijr
174 5 5 7

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

1 answer: sort newest

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 '12 at 11:37 AM

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 39 43 69

Nice, but yeah I think this will have to do... thanks @kev

Jun 15 '12 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.

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x116

asked: Jun 15 '12 at 11:15 AM

Seen: 588 times

Last Updated: Jun 15 '12 at 12:20 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.