I have this super long SQL query, now what this query does is the following, creates the first level which is the Job_No....next in the second level gets all the 'BaselineStart' however this separates each BaselineStart with baseOrSchedStartList, what I am looking to do is have 1 baseOrSchedStartList for each Job_No and have each 'string' under baseOrSchedStartList. I hope this makes sense. Here is my query:
SELECT [Job_No] as '@Key',
(
SELECT ISNULL(UserDate1,ScheduleTasks.BaselineStart) AS 'string'
FROM ScheduleTasks INNER JOIN Schedule ON ScheduleTasks.ScheduleID = Schedule.ScheduleID INNER JOIN V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = Schedule.Job_No WHERE Job_No IN (SELECT JOB_NUMBER
FROM V_CONSTAT_ACTUAL_DATES
WHERE AREA_DESC = 'Orchard Park'
AND (DATE_TO_END IS NOT NULL AND DATE_TO_END >= GETDATE())) AND
(
LibraryTaskID <> 203
AND LibraryTaskID <> 204
AND LibraryTaskID <> 210
AND LibraryTaskID <> 211
AND LibraryTaskID <> 214
AND LibraryTaskID <> 215
AND LibraryTaskID <> 218
AND LibraryTaskID <> 219
AND LibraryTaskID <> 224
AND LibraryTaskID <> 227
AND LibraryTaskID <> 230
AND LibraryTaskID <> 231
AND LibraryTaskID <> 232
AND LibraryTaskID <> 233
AND LibraryTaskID <> 234
AND LibraryTaskID <> 235
AND LibraryTaskID <> 236
AND LibraryTaskID <> 237
AND LibraryTaskID <> 238
AND LibraryTaskID <> 239
AND LibraryTaskID <> 240
AND LibraryTaskID <> 242
AND LibraryTaskID <> 243
AND LibraryTaskID <> 295
AND LibraryTaskID <> 299
AND LibraryTaskID <> 303
AND LibraryTaskID <> 304
AND LibraryTaskID <> 305
AND LibraryTaskID <> 313
AND LibraryTaskID <> 314
AND LibraryTaskID <> 321
AND LibraryTaskID <> 333
)
ORDER BY DATE_TO_END, SortOrder
FOR XML PATH('baseOrSchedStartList'), Type
)
FROM ScheduleTasks INNER JOIN Schedule ON ScheduleTasks.ScheduleID = Schedule.ScheduleID INNER JOIN V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = Schedule.Job_No WHERE Job_No IN (SELECT JOB_NUMBER
FROM V_CONSTAT_ACTUAL_DATES
WHERE AREA_DESC = 'Orchard Park'
AND (DATE_TO_END IS NOT NULL AND DATE_TO_END >= GETDATE())) AND
(
LibraryTaskID <> 203
AND LibraryTaskID <> 204
AND LibraryTaskID <> 210
AND LibraryTaskID <> 211
AND LibraryTaskID <> 214
AND LibraryTaskID <> 215
AND LibraryTaskID <> 218
AND LibraryTaskID <> 219
AND LibraryTaskID <> 224
AND LibraryTaskID <> 227
AND LibraryTaskID <> 230
AND LibraryTaskID <> 231
AND LibraryTaskID <> 232
AND LibraryTaskID <> 233
AND LibraryTaskID <> 234
AND LibraryTaskID <> 235
AND LibraryTaskID <> 236
AND LibraryTaskID <> 237
AND LibraryTaskID <> 238
AND LibraryTaskID <> 239
AND LibraryTaskID <> 240
AND LibraryTaskID <> 242
AND LibraryTaskID <> 243
AND LibraryTaskID <> 295
AND LibraryTaskID <> 299
AND LibraryTaskID <> 303
AND LibraryTaskID <> 304
AND LibraryTaskID <> 305
AND LibraryTaskID <> 313
AND LibraryTaskID <> 314
AND LibraryTaskID <> 321
AND LibraryTaskID <> 333
)
GROUP BY [Job_No]
FOR XML PATH('Job_No'), ROOT('Root')
This query returns this data:
<Root>
<Job_No Key="ORC0023">
<baseOrSchedStartList>
<string>2015-09-11T08:00:00</string>
</baseOrSchedStartList>
<baseOrSchedStartList>
<string>2015-08-10T16:00:00</string>
</baseOrSchedStartList>
<baseOrSchedStartList>
<string>2015-08-11T16:00:00</string>
</baseOrSchedStartList>
</Job_No>
</Root>
what I am looking for is the following:
<Root>
<Job_No Key="ORC0023">
<baseOrSchedStartList>
<string>2015-09-11T08:00:00</string>
<string>2015-08-10T16:00:00</string>
<string>2015-08-11T16:00:00</string>
</baseOrSchedStartList>
</Job_No>
</Root>
Any help would be much appreciated.
If you just take away the @ from the AS [@Key] you'll get this
DECLARE @tbl TABLE([Key] VARCHAR(10),DateValue DATETIME);
INSERT INTO @tbl VALUES ('ORC0023','2015-09-11T08:00:00')
,('ORC0023','2015-08-10T16:00:00')
,('ORC0023','2015-08-11T16:00:00')
DECLARE @UniqueKey VARCHAR(10)='ORC0023';
SELECT @UniqueKey AS [Key]
,(
SELECT DateValue AS [string]
FROM @tbl AS tbl
WHERE tbl.[Key]=@UniqueKey
FOR XML PATH(''),TYPE
) AS baseOrSchedStartList
FOR XML PATH('Job_No'),ROOT('Root')
/*
<Root>
<Job_No>
<Key>ORC0023</Key>
<baseOrSchedStartList>
<string>2015-09-11T08:00:00</string>
<string>2015-08-10T16:00:00</string>
<string>2015-08-11T16:00:00</string>
</baseOrSchedStartList>
</Job_No>
</Root>
*/
Try to change the as '@Key' to as Key
and the
ORDER BY DATE_TO_END, SortOrder
FOR XML PATH('baseOrSchedStartList'), Type
)
to
ORDER BY DATE_TO_END, SortOrder
FOR XML PATH(''), Type
) AS baseOrSchedStartList
Another alternative was - as pointed out by BateTech in a comment - to put your "baseOrSchedStart" as ,ROOT('baseOrSchedStart') behind the FOR XML PATH('') and let the paranthesis unnamed...
Without your table's structures and test data it is difficult to fully understand your query (which isn't that super long actually :-) )
Therefore I prepared a simplified structure example and hope, that you can understand the approach and transfer this to your actual data
Just try the following:
DECLARE @tbl TABLE([Key] VARCHAR(10),DateValue DATETIME);
INSERT INTO @tbl VALUES ('ORC0023','2015-09-11T08:00:00')
,('ORC0023','2015-08-10T16:00:00')
,('ORC0023','2015-08-11T16:00:00')
DECLARE @UniqueKey VARCHAR(10)='ORC0023';
--This is structurally what you've got
SELECT @UniqueKey AS [@Key]
,(
SELECT DateValue AS [string]
FROM @tbl AS tbl
WHERE tbl.[Key]=@UniqueKey
FOR XML PATH('baseOrSchedStartList'),TYPE
)
FOR XML PATH('Job_No'),ROOT('Root')
/*
<Root>
<Job_No Key="ORC0023">
<baseOrSchedStartList>
<string>2015-09-11T08:00:00</string>
</baseOrSchedStartList>
<baseOrSchedStartList>
<string>2015-08-10T16:00:00</string>
</baseOrSchedStartList>
<baseOrSchedStartList>
<string>2015-08-11T16:00:00</string>
</baseOrSchedStartList>
</Job_No>
</Root>
*/
Now try it like this
SELECT @UniqueKey AS [@Key]
,(
SELECT DateValue AS [*]
FROM @tbl AS tbl
WHERE tbl.[Key]=@UniqueKey
FOR XML PATH('string'),TYPE
) AS baseOrSchedStartList
FOR XML PATH('Job_No'),ROOT('Root')
/*
<Root>
<Job_No Key="ORC0023">
<baseOrSchedStartList>
<string>2015-09-11T08:00:00</string>
<string>2015-08-10T16:00:00</string>
<string>2015-08-11T16:00:00</string>
</baseOrSchedStartList>
</Job_No>
</Root>
*/
Btw: This is the same as this:
SELECT @UniqueKey AS [@Key]
,(
SELECT DateValue AS [string]
FROM @tbl AS tbl
WHERE tbl.[Key]=@UniqueKey
FOR XML PATH(''),TYPE
) AS baseOrSchedStartList
FOR XML PATH('Job_No'),ROOT('Root')