Background: We use Teradata to house the database which is then carried over to AC; I am not entirely certain how that works, but that should be irrelevant. Because of this setup, regular query activities do not always work, therefore we have to use SQL/JS activities to get data reliably.
Situation: I made a custom report that lists existing workflows that have affinity set to a non-empty value. Code is available at the end of this comment.
The report in question gets us Wf int. name, Camp. int. name, Last modified by etc. as well as "Affinity of the next task" as sAffinity.
I would like to change that to "Default affinity", but that is where I hit the wall.
Affinity of the next task is said to be an SQL field, which works just fine...
Whereas Default affinity is an XML attribute, which I have been unable to implement into the Select statement.
Does anybody happen to know how to get the value of this "XML attribute" using SQL as in the existing code below?
//Select data from the DB
var sql = "SELECT XtkWorkflow.sLabel AS sWfName, XtkWorkflow.sInternalName AS sIntName, NmsOperation.sInternalName AS sCampInt, XtkWorkflow.sLogin AS sOwner, XtkWorkflow.sNextProcessingAfn AS sAffinity, XtkWorkflow.iState, XtkWorkflow.tsLastModified FROM XtkWorkflow JOIN NmsOperation ON XtkWorkflow.iOperationId=NmsOperation.iOperationId WHERE XtkWorkflow.sNextProcessingAfn IS NOT NULL ORDER BY XtkWorkflow.tsLastModified";
var sqlRes = sqlSelect("resSQLResult,@sWfName:string,@sIntName:string,@sCampInt:string,@sOwner:string,@sAffinity:string,@iState:string,@tsLastModified:date", sql);