Custom SQL Variable error in send email trigger action

sburns

I am working on trying to get a list of VMs associated with a VM host into the email notification when a VM host fails. I've put together a SQL query that pulls that information successfully in Database Manager and puts it all together into a single cell, however, when I insert that query into a variable it returns this error:

MACRO SQL ERROR - No column name was specified for column 1 of 'fersofzx_iz5'.

The name of column 1 changes each time I simulate the trigger action

Here is the query, that is working from Database Manager:

${SQL: SELECT VirtualMachines.Name
FROM [dbo].[VirtualMachines]
INNER JOIN [dbo].[VIM_Hosts] ON VIM_Hosts.HostID=VirtualMachines.HostID
WHERE VIM_Hosts.NodeID='${N=SwisEntity;M=NodeID}'
FOR XML PATH ('')}

sburns

I have also, after reading past posts on this issue, attempted to assign an alias to that column without success

${SQL: SELECT (VirtualMachines.Name) as VMName
FROM [dbo].[VirtualMachines]
INNER JOIN [dbo].[VIM_Hosts] ON VIM_Hosts.HostID=VirtualMachines.HostID
WHERE VIM_Hosts.NodeID='${N=SwisEntity;M=NodeID}'
FOR XML PATH ('')}