I have been tasked with creating the following XML without utilising FOR XML EXPLICIT.

I attach the XML and scripts for sample tables and data.

Any help appreciated.

Thanks in advance

-- The required XML output

<LIST>
  <RPTRestriction RestrictionID="2" RestrictionDescription="00:00 - 06:00 (Mon-Fri,Sat)" UpdateUserID="4307"  />
  <SELECTED>
    <RPTSite SiteID="3251" PlaceReference="6222" PlaceName="Liverpool Walton Metro" SiteTypeName="STORE" />
    <RPTSite SiteID="581" PlaceReference="2461" PlaceName="Edinburgh Colinton" SiteTypeName="STORE" />
    <RPTSite SiteID="273" PlaceReference="2295" PlaceName="Connswater" SiteTypeName="STORE" />
    <RPTSite SiteID="279" PlaceReference="2678" PlaceName="Holywood" SiteTypeName="STORE" />
  </SELECTED>
  <NOTSELECTED>
    <RPTSite SiteID="1" PlaceReference="5971200" PlaceName="B - Cameo Cakes" SiteTypeName="STORE" />
    <RPTSite SiteID="2" PlaceReference="5482700" PlaceName="B - Kerry Foods Tralee" SiteTypeName="STORE" />
    <RPTSite SiteID="3" PlaceReference="3688" PlaceName="Tralee Square" SiteTypeName="STORE" />
    <RPTSite SiteID="4" PlaceReference="3684" PlaceName="Tralee" SiteTypeName="STORE" />
    <RPTSite SiteID="5" PlaceReference="3513" PlaceName="Killarney Store" SiteTypeName="STORE" />
  </NOTSELECTED>
  <RPTSiteType SiteTypeID="3" SiteTypeName="STORE" SiteTypeDescription="Store" />
</LIST>

CREATE TABLE dbo.Restriction 
(RestrictionID int, RestrictionDescription varchar(50)
, UpdateUserID int, UpdateDate datetime)
CREATE TABLE dbo.SiteRestriction (SiteRestrictionID int, SiteID int, RestrictionID int)
CREATE TABLE dbo.Site (SiteID int, SiteTypeID int, PlaceID int)
CREATE TABLE dbo.Place (PlaceID int, PlaceName varchar(50), PlaceReference varchar(50))
CREATE TABLE dbo.SiteType (SiteTypeID int, SiteTypeName varchar(50), SiteTypeDescription varchar(50))

INSERT dbo.Restriction 
SELECT 1, '01:00 - 02:00 Sunday',1,GETDATE()
INSERT dbo.Restriction 
SELECT 2, '00:00 - 06:00 (Mon-Fri,Sat)',4307,'20100718 14:42:43.527'
INSERT dbo.SiteRestriction SELECT 1, 3251, 2
INSERT dbo.SiteRestriction SELECT 1, 581, 2
INSERT dbo.SiteRestriction SELECT 1, 273, 2
INSERT dbo.SiteRestriction SELECT 1, 279, 2
INSERT dbo.Site SELECT 1, 3, 1000
INSERT dbo.Site SELECT 2, 3, 2000
INSERT dbo.Site SELECT 3, 3, 3000
INSERT dbo.Site SELECT 4, 3, 4000
INSERT dbo.Site SELECT 5, 3, 5000
INSERT dbo.Site SELECT 3251, 3, 32510
INSERT dbo.Site SELECT 581, 3, 5810
INSERT dbo.Site SELECT 273, 3, 2730
INSERT dbo.Site SELECT 279, 3, 2790
INSERT dbo.Place SELECT 1000, 'B - Cameo Cakes','5971200'
INSERT dbo.Place SELECT 2000, 'B - Kerry Foods Tralee','5482700'
INSERT dbo.Place SELECT 3000, 'Tralee Square','3688'
INSERT dbo.Place SELECT 4000, 'Tralee','3684'
INSERT dbo.Place SELECT 5000, 'Killarney Store','3513'
INSERT dbo.Place SELECT 32510, 'Liverpool Walton Metro','6222'
INSERT dbo.Place SELECT 5810, 'Edinburgh Colinton','2461'
INSERT dbo.Place SELECT 2730, 'Connswater','2295'
INSERT dbo.Place SELECT 2790, 'Holywood','2678'
INSERT dbo.SiteType SELECT 1, 'Other','Other'
INSERT dbo.SiteType SELECT 2, 'Depot','Depot'
INSERT dbo.SiteType SELECT 3, 'STORE','Store'

-- <RPTRestriction> data
SELECT RestrictionID,RestrictionDescription,UpdateUserID,UpdateDate FROM dbo.Restriction 
WHERE RestrictionID = 2

-- <SELECTED> data
SELECT Site.SiteID, Place.PlaceReference, Place.PlaceName, SiteType.SiteTypeName 
FROM dbo.SiteType SiteType 
INNER JOIN dbo.Site Site ON SiteType.SiteTypeID = Site.SiteTypeID
INNER JOIN dbo.Place Place ON Site.PlaceID = Place.PlaceID
INNER JOIN dbo.SiteRestriction SiteRestriction 
ON Site.SiteID = SiteRestriction.SiteID
 AND SiteRestriction.RestrictionID = 2
WHERE SiteType.SiteTypeName = 'STORE'

-- <NOTSELECT> data
SELECT Site.SiteID, Place.PlaceReference, Place.PlaceName, SiteType.SiteTypeName 
FROM dbo.SiteType SiteType 
INNER JOIN dbo.Site Site ON SiteType.SiteTypeID = Site.SiteTypeID
INNER JOIN dbo.Place Place ON Site.PlaceID = Place.PlaceID
LEFT JOIN dbo.SiteRestriction SiteRestriction 
ON Site.SiteID = SiteRestriction.SiteID 
AND SiteRestriction.RestrictionID = 2
WHERE SiteType.SiteTypeName = 'STORE'
AND SiteRestriction.SiteID IS NULL

--<RPTSiteType> data
select SiteTypeID, SiteTypeName, SiteTypeDescription 
FROM dbo.SiteType WHERE SiteTypeName = 'STORE'

Submitted under: XML ·  ·  ·  ·