Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

SQL Select statement - XML attribute

Avatar

Avatar
Boost 1
Level 1
Dava27
Level 1

Like

1 like

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Dava27
Level 1

Like

1 like

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
Dava27
Level 1

19-11-2019

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...

1856930_pastedImage_4.png

Whereas Default affinity is an XML attribute, which I have been unable to implement into the Select statement.

1856931_pastedImage_5.png

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

Replies

Avatar

Avatar
Contributor
MVP
Milan_Vucetic
MVP

Likes

166 likes

Total Posts

384 posts

Correct Reply

135 solutions
Top badges earned
Contributor
Springboard
Shape 1
Validate 25
Validate 10
View profile

Avatar
Contributor
MVP
Milan_Vucetic
MVP

Likes

166 likes

Total Posts

384 posts

Correct Reply

135 solutions
Top badges earned
Contributor
Springboard
Shape 1
Validate 25
Validate 10
View profile
Milan_Vucetic
MVP

19-11-2019

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

Avatar

Avatar
Validate 1
Level 4
somasundaramhal
Level 4

Likes

73 likes

Total Posts

71 posts

Correct Reply

12 solutions
Top badges earned
Validate 1
Shape 1
Boost 50
Boost 5
Boost 3
View profile

Avatar
Validate 1
Level 4
somasundaramhal
Level 4

Likes

73 likes

Total Posts

71 posts

Correct Reply

12 solutions
Top badges earned
Validate 1
Shape 1
Boost 50
Boost 5
Boost 3
View profile
somasundaramhal
Level 4

20-11-2019

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.