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

avatar image

bonskijr
204 5 5 10

(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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

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.

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:

x186

asked: Jun 15, 2012 at 11:15 AM

Seen: 2102 times

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

Copyright 2016 Redgate Software. Privacy Policy