May 6, 2013 at 9:07 am
Hello,
I have the below SQL which is working great except for the <Type> element. All the types are appearing on the very last record rather than listed for each <BookingID> as I would like. Any advice or help is appreciated!
Here is the SQL:
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'root!1!',
NULL AS 'Event!2!BookingID!Element',
NULL AS 'Event!2!EventName!cdata',
NULL AS 'Event!2!TimeBookingStart!Element',
NULL AS 'Event!2!TimeBookingEnd!Element',
NULL AS 'Event!2!URL!Element',
NULL AS 'Event!2!Description!cdata',
NULL AS 'Event!2!Room!Element',
NULL AS 'Event!2!Audience!Element',
NULL AS 'Type!3!!cdata'
UNION ALL
SELECT DISTINCT
2 AS Tag,
1 AS Parent,
NULL,
BookingID,
WebTitle,
TimeBookingStart,
TimeBookingEnd,
URL,
[Description],
Room,
Audience,
NULL
FROM WEBSITE_EVENTS2
UNION ALL
SELECT DISTINCT
3 AS Tag,
2 AS Parent,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, EventType
FROM WEBSITE_EVENTS2
FOR XML EXPLICIT
XML Output: (The <Type> for bookingId 14991 are displaying under bookingId 14992)
<Event>
<BookingID>14991</BookingID>
<EventName><![CDATA[After Work Expeditions]]></EventName>
<TimeBookingStart>2013-07-17T14:30:00</TimeBookingStart>
<TimeBookingEnd>2013-07-17T16:00:00</TimeBookingEnd>
<URL></URL>
<Description><![CDATA[Text description here]]></Description>
<Room>Room 1300L</Room>
<Audience>Public</Audience>
</Event>
<Event>
<BookingID>14992</BookingID>
<EventName><![CDATA[Trustee Meeting]]></EventName>
<TimeBookingStart>2013-07-19T11:00:00</TimeBookingStart>
<TimeBookingEnd>2013-07-19T12:00:00</TimeBookingEnd>
<URL></URL>
<Description><![CDATA[This event is closed to the public. ]]></Description>
<Room>Room 1300L</Room>
<Audience>Private</Audience>
<Type><![CDATA[Arts & Theatre]]></Type>
<Type><![CDATA[Conference]]></Type>
<Type><![CDATA[Lectures & Discussions]]></Type>
<Type><![CDATA[Music & Entertainment]]></Type>
</Event>
There is a separate record for each bookingID/Type in the database. For example:
BookingID EventType
--------- -----------
14991 Music & Entertainment
14991 Arts & Theatre
14992 Lectures & Discussions
14992 Conference
May 6, 2013 at 10:29 am
For future reference, Please read this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
For the event types you need to put the booking ID in the output.
And you MUST use an order by to get the xml to structure properly.
CREATE TABLE #WEBSITE_EVENTS2
(
BookingID INT,
WebTitle varchar(100),
TimeBookingStart datetime,
TimeBookingEnd datetime,
URL VARCHAR(100),
[Description] VARCHAR(100),
Room VARCHAR(100),
Audience VARCHAR(100),
EventType VARCHAR(100)
)
INSERT INTO #WEBSITE_EVENTS2
(BookingID,
WebTitle,
TimeBookingStart,
TimeBookingEnd,
URL,
Description,
Room,
Audience,
EventType)
VALUES
(14991, -- BookingID - int
'After Work Expeditions', -- WebTitle - varchar(100)
'2013-07-17T14:30:00', -- TimeBookingStart - datetime
'2013-07-17T16:00:00', -- TimeBookingEnd - datetime
'SomeURL', -- URL - varchar(100)
'Text description here', -- Description - varchar(100)
'Room 1300L', -- Room - varchar(100)
'Public', -- Audience - varchar(100)
'Music & Entertainment' -- EventType - varchar(100)
),(14991, -- BookingID - int
'After Work Expeditions', -- WebTitle - varchar(100)
'2013-07-17T14:30:00', -- TimeBookingStart - datetime
'2013-07-17T16:00:00', -- TimeBookingEnd - datetime
'SomeURL', -- URL - varchar(100)
'Text description here', -- Description - varchar(100)
'Room 1300L', -- Room - varchar(100)
'Public', -- Audience - varchar(100)
'Arts & Theatre' -- EventType - varchar(100)
),(14992, -- BookingID - int
'Trustee Meeting', -- WebTitle - varchar(100)
'2013-07-19T11:00:00', -- TimeBookingStart - datetime
'2013-07-19T12:00:00', -- TimeBookingEnd - datetime
'SomeURL', -- URL - varchar(100)
'This event is closed to the public.', -- Description - varchar(100)
'Room 1300L', -- Room - varchar(100)
'Private', -- Audience - varchar(100)
'Lectures & Discussions' -- EventType - varchar(100)
),(14992, -- BookingID - int
'Trustee Meeting', -- WebTitle - varchar(100)
'2013-07-19T11:00:00', -- TimeBookingStart - datetime
'2013-07-19T12:00:00', -- TimeBookingEnd - datetime
'SomeURL', -- URL - varchar(100)
'This event is closed to the public.', -- Description - varchar(100)
'Room 1300L', -- Room - varchar(100)
'Private', -- Audience - varchar(100)
'Conference ' -- EventType - varchar(100)
)
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'root!1!',
NULL AS 'Event!2!BookingID!Element',
NULL AS 'Event!2!EventName!cdata',
NULL AS 'Event!2!TimeBookingStart!Element',
NULL AS 'Event!2!TimeBookingEnd!Element',
NULL AS 'Event!2!URL!Element',
NULL AS 'Event!2!Description!cdata',
NULL AS 'Event!2!Room!Element',
NULL AS 'Event!2!Audience!Element',
NULL AS 'Type!3!!cdata'
UNION ALL
SELECT DISTINCT
2 AS Tag,
1 AS Parent,
NULL,
BookingID,
WebTitle,
TimeBookingStart,
TimeBookingEnd,
URL,
[Description],
Room,
Audience,
NULL
FROM #WEBSITE_EVENTS2
UNION ALL
SELECT DISTINCT
3 AS Tag,
2 AS Parent,
NULL,
BookingID, -- You need to add this here
NULL, NULL, NULL, NULL, NULL, NULL, NULL, EventType
FROM #WEBSITE_EVENTS2
ORDER BY 'Event!2!BookingID!Element' -- Must Add OrderBy
FOR XML EXPLICIT
Output
<root>
<Event>
<BookingID>14991</BookingID>
<EventName><![CDATA[After Work Expeditions]]></EventName>
<TimeBookingStart>2013-07-17T14:30:00</TimeBookingStart>
<TimeBookingEnd>2013-07-17T16:00:00</TimeBookingEnd>
<URL>SomeURL</URL>
<Description><![CDATA[Text description here]]></Description>
<Room>Room 1300L</Room>
<Audience>Public</Audience>
<Type><![CDATA[Arts & Theatre]]></Type>
<Type><![CDATA[Music & Entertainment]]></Type>
</Event>
<Event>
<BookingID>14992</BookingID>
<EventName><![CDATA[Trustee Meeting]]></EventName>
<TimeBookingStart>2013-07-19T11:00:00</TimeBookingStart>
<TimeBookingEnd>2013-07-19T12:00:00</TimeBookingEnd>
<URL>SomeURL</URL>
<Description><![CDATA[This event is closed to the public.]]></Description>
<Room>Room 1300L</Room>
<Audience>Private</Audience>
<Type><![CDATA[Conference ]]></Type>
<Type><![CDATA[Lectures & Discussions]]></Type>
</Event>
</root>
May 6, 2013 at 10:54 am
Thank you very much! That worked.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply