How to find back end SQL tables for Inherited Properties

If you have a working query from the API (SWQL Studio), then you can add "WITH QUERYSTATS" to get the SQL behind the scenes.  That should give you the info you need.

I'd still recommend NOT doing this work in SQL if at all possible.  What are you trying to accomplish/reference?

Setting up an alert for WPM transactions, but due to the way the ticketing integration works I need to bind on the node. I also want to include in the alert text which transaction steps have failed, but due to another limitation in the ticketing integration I can't pass across multiple rows, so need a way to output several rows as a single string. The only way I've found to do that so far is using the FOR XML function in a SQL query.

Sounds like you'd need to pull the parent node and then concatenate the error messages.  I don't think it's impossible to do, but it would be a little complex - in either SQL or SWQL.

I tend to agree with KMSigma.SWI about using the concat command. You can make complex joins in SWQL, and SWQL generally protects you from upgrade pits of doom and DB schema changes. You can always post the where are getting stuck in the forums, and usually one of us has run into that query or similar. I cannot say how often I have been able to get help on THWACK regarding SWQL. 

I tend to agree with KMSigma.SWI about using the concat command. You can make complex joins in SWQL, and SWQL generally protects you from upgrade pits of doom and DB schema changes. You can always post the where are getting stuck in the forums, and usually one of us has run into that query or similar. I cannot say how often I have been able to get help on THWACK regarding SWQL.