SQL Select statement - XML attribute

[Dava27]

Hi,

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?

________________start_____________________

//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);

//Build the table

vars.tblStrucWKF = '<TABLE CELLPADDING="0" CELLSPACING="0" BORDER="1" WIDTH="1200"><TR bgcolor="#C0C0C0"><TH>Workflow&#160;name</TH><TH>Wf&#160;Int.&#160;name</TH><TH>Campaign&#160;Int.&#160;name</TH><TH>Owner</TH><TH>Affinity</TH><TH>WF Status</TH><TH>Last Modified</TH>';

//Loop through returned query and bring in results

for each (var resSQLResult in sqlRes) {

vars.tblStrucWKF += "<TR  style='text-align:center'><TD>"+resSQLResult.@sWfName+"</TD><TD>"+resSQLResult.@sIntName+"</TD><TD>"+resSQLResult.@sCampInt+"</TD><TD>"+resSQLResult.@sOwner+"</TD><TD>"+resSQLResult.@sAffinity+"</TD><TD>"+resSQLResult.@iState+"</TD><TD>"+resSQLResult.@tsLastModified+"</TD>";

}

//Close the table tag

vars.tblStrucWKF += "</TABLE>";

vars.True = sqlRes;

_________________end____________________

Thank you,
David

[Milan_Vucetic]

Hi David,

If I am understanding well you need extract specific value from the MEMO (XML) field.

The best way to do this is use regular expression (regex). You may use REGEXP_SUBSTR function which returns the actual substring matching the regular expression pattern you specify.

Check more on: 12 Using Regular Expressions With Oracle Database

Regards,

Milan

[somasundaramhal]

Hi,

I'm not sure how to retrieve XML in SQL inside AC. However, you can use querydef for the same.

   var query= xtk.queryDef.create(<queryDef schema="xtk:workflow" operation="select">

    <select>

      <node expr="@affinity" />

      <node expr="@label" />

    </select>

    <where>

      <condition expr="@id != ''"/>

    </where>

    </queryDef>);

    var result = query.ExecuteQuery();

    for each(var r in result ) {

      logInfo(r.@affinity + '************************' + r.@label);

    }

This works for me.