ORDER BY XML and COALESCE

1

SQL Server 2016.

I am parsing SSAS 2016 extended events on the relational engine and have run into something strange.

In BOL it states that ORDER BY cannot be used with various data types including XML. Fine.

While parsing the XML I have the columns aliased.

I can do an ORDER BY with the column alias but if I try to include a COALESCE function or ISNULL I get errors (specifically Invalid column name StartTime/EndTime).

I thought that the ORDER BY is performed by the engine towards the end (hence ORDER BY using position number is possible) and this behavior is strange.

Anyone else see this?

Is the BOL statement a CYA thing and the use of ORDER BY will work (without the function calls obviously) but is not guaranteed due to the XML datatype?

/* works */
SELECT xe.FILE_NAME As TraceFileName,
       xe.EVENT_NAME As xEventName,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ServerName"]/value)[1]','varchar(50)') AS ServerName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="NTUserName"]/value)[1]','varchar(50)') AS NTUserName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="NTCanonicalUserName"]/value)[1]','varchar(50)') AS NTCanonicalUserName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ConnectionID"]/value)[1]','int') AS ConnectionID, 
	  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()), CONVERT(XML, xe.xml_data).value('(/event/data[@name="StartTime"]/value)[1]','datetime')) As StartTime,
       DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()), CONVERT(XML, xe.xml_data).value('(/event/data[@name="EndTime"]/value)[1]','datetime')) As EndTime,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="Duration"]/value)[1]','bigint') AS Duration,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="TextData"]/value)[1]','varchar(max)') AS TextData,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ClientHostName"]/value)[1]','varchar(50)') AS ClientHostName
  FROM dbo.SSASLogsLoad As xe
 WHERE xe.EVENT_NAME = 'AuditLogout'
    OR xe.EVENT_NAME = 'Error'
 ORDER BY StartTime, EndTime;
/* does not work */
SELECT xe.FILE_NAME As TraceFileName,
       xe.EVENT_NAME As xEventName,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ServerName"]/value)[1]','varchar(50)') AS ServerName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="NTUserName"]/value)[1]','varchar(50)') AS NTUserName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="NTCanonicalUserName"]/value)[1]','varchar(50)') AS NTCanonicalUserName, 
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ConnectionID"]/value)[1]','int') AS ConnectionID, 
	  DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()), CONVERT(XML, xe.xml_data).value('(/event/data[@name="StartTime"]/value)[1]','datetime')) As StartTime,
       DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()), CONVERT(XML, xe.xml_data).value('(/event/data[@name="EndTime"]/value)[1]','datetime')) As EndTime,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="Duration"]/value)[1]','bigint') AS Duration,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="TextData"]/value)[1]','varchar(max)') AS TextData,
       CONVERT(XML, xe.xml_data).value('(/event/data[@name="ClientHostName"]/value)[1]','varchar(50)') AS ClientHostName
  FROM dbo.SSASLogsLoad As xe
 WHERE xe.EVENT_NAME = 'AuditLogout'
    OR xe.EVENT_NAME = 'Error'
 ORDER BY COALESCE(StartTime, EndTime);
2

This is the expected behavior. When you use an expression in the ORDER BY clause, SQL Server tries to resolve it by evaluating the original column/expression. See an example in the response here.

You can get around this by wrapping your query in a cte (or subquery) and selecting and ordering from the cte.

SELECT * FROM
( Your original query with no order by clause here)
ORDER BY COALESCE(StartTime, EndTime)

But, logically, your first query would do exactly the same thing, so this subquery is an unnecessary step.

3

A cludge that I use is to add a column to the SELECT, with Alias Name "OrderBy" or somesuch, which is the COALESCE that you need, and then include that Alias Name in the ORDER BY.

If it needs columns from the SELECT, which are themselves non-trivial, then I put those manipulations in a CROSS APPLY, or a CTE, so that I only have to code them once.

4

Thank you all for your responses!