Sql For Xml List Of Nested Rows

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' ho

Solution 1:

UPDATE According to your follow up question

If you just take away the @ from the AS [@Key] you'll get this

DECLARE@tblTABLE([Key] VARCHAR(10),DateValue DATETIME);
INSERTINTO@tblVALUES ('ORC0023','2015-09-11T08:00:00')
                       ,('ORC0023','2015-08-10T16:00:00')
                       ,('ORC0023','2015-08-11T16:00:00')

DECLARE@UniqueKeyVARCHAR(10)='ORC0023';
SELECT@UniqueKeyAS [Key]
      ,(
        SELECT DateValue AS [string]
        FROM@tblAS tbl 
        WHERE tbl.[Key]=@UniqueKeyFOR 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>
*/

UPDATE2 For your actual query this means (probably)

Try to change the as '@Key' to as Key

and the

ORDER BY DATE_TO_END, SortOrder
        FOR XML PATH('baseOrSchedStartList'), Type
)

to

ORDERBY 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...

previous

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@tblTABLE([Key] VARCHAR(10),DateValue DATETIME);
INSERTINTO@tblVALUES ('ORC0023','2015-09-11T08:00:00')
                       ,('ORC0023','2015-08-10T16:00:00')
                       ,('ORC0023','2015-08-11T16:00:00')

DECLARE@UniqueKeyVARCHAR(10)='ORC0023';

--This is structurally what you've gotSELECT@UniqueKeyAS [@Key]
      ,(
        SELECT DateValue AS [string]
        FROM@tblAS tbl 
        WHERE tbl.[Key]=@UniqueKeyFOR 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 @UniqueKeyAS[@Key]
      ,(
        SELECT DateValue AS [*]
        FROM @tbl AS tbl 
        WHERE tbl.[Key]=@UniqueKey
        FOR XML PATH('string'),TYPE
       ) ASbaseOrSchedStartListFORXMLPATH('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 @UniqueKeyAS[@Key]
      ,(
        SELECT DateValue AS [string]
        FROM @tbl AS tbl 
        WHERE tbl.[Key]=@UniqueKey
        FOR XML PATH(''),TYPE
       ) ASbaseOrSchedStartListFORXMLPATH('Job_No'),ROOT('Root')