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:
1
Apparel
2
Pizzeria
3
All Restaurants
4
Towing
How can I make the above query generate the following XML:
1
Apparel
2
Pizzeria
3
All Restaurants
4
Towing
Best Answer
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 ....
Nice, but yeah I think this will have to do... thanks @kev
