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
asked
Jun 15, 2012 at 11:15 AM
in Default
bonskijr
204
●
5
●
5
●
10