Returning SQL within email alert

Here's an Idea.  Although not exactly what you want. Don't see why it wouldn't work.

Turn your SQL into a report (you can also turn the report into a resource to make a custom view)

Schedule Report in report Scheduler and pick your email recipients

Go to your Orion directory and look for Job-xxxxxx files

Find the one for the Report you Created and Scheduled (you'll need to open them up to look)

In your alert add Run External Program.  \orion\runorionjob.exe [your job name] you may need the path in the job name.

The report will be emailed shortly after the alert arrives

Try:

${SQL: select Caption+", " as a from Nodes Where CORESITE = '${Node.CORESITE}' FOR XML PATH}

May have some silly stuff in it, but should give you a "," separated list.