April 2, 2015 at 1:51 pm
Hello,
I am looking for help to solve my query within a XML nest.
I have two temp tables being used:
1. #Event_Data that will get the details of an event (in the below example the Event Name)
2.#TempGetLoc that will get the location(s) of the event
When running the query below, my expectation is the nested query using #GetLocName would produce only the locations associated with the specific Event ID joining by EventID and EventDate. However, what is happening is I am getting all locations in the #TempGetLoc table whether or not.
I am looking for advice on what I am doing wrong. Do I need some kind of loop in this case based on EventID and EventDate to only produce the locations associated with specific event?
SELECT
'group'AS '@type',
'text'AS 'Name/@type',
EventNameAS 'Name',
(
(
SELECT
'group'AS '@type',
'group'AS 'Location/@type',
LocationAS 'LocationName',
FROM #TempGetLoc loc INNER JOIN #Event_Data ed ON ed.EventID=loc.EventID and ed.EventDate = loc.EventDate
GROUP BY location
FOR XML PATH('Location'),ROOT('Locations'),
TYPE
)
),
FROM #Event_DATA
FOR XML PATH('EVENT'), ELEMENTS
April 2, 2015 at 2:21 pm
Do you have some DDL and test data so we can re-create and populate your two temp tables?
-- Itzik Ben-Gan 2001
April 4, 2015 at 3:22 pm
Quick question, did you have a look at this thread?
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply