Fetch a parameter field/value

Avatar

Avatar
Validate 1
Level 1
MeMonty
Level 1

Likes

4 likes

Total Posts

9 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
View profile

Avatar
Validate 1
Level 1
MeMonty
Level 1

Likes

4 likes

Total Posts

9 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
View profile
MeMonty
Level 1

17-03-2017

Hi,

I have a built the following workflow to extract Campaigns, Delivery associated each Campaign, Delivery Logs of each of the Deliveries

Workflow.PNG

Stage-1 : Query Campaign

This extracts all the Campaigns.

Output Fields: Campaign Id, Campaign Label

Stage-2 : Enrichment with Delivery

This extracts the Delivery associated with each of the Campaigns extracted in Stage-1.

Output Fields: Campaign Id, Campaign Label, Delivery Id, Delivery Label

Stage-3 : Enrichment with Delivery Logs

This extracts the Delivery Logs associated with each of the Delivery extracted in Stage-2.

Output Fields: Campaign Id, Campaign Label, Delivery Id, Delivery Label

Stage-4 : Change dimension - Delivery Log

This changes the target dimension to Delivery Logs.

Stage-5 : Enrichment – Campaigns Deliveries and Delivery Logs

This enrichment simply adds the Delivery Logs fields to output. In Stage-3, it simply joined to Delivery Logs but any of the Delivery Logs fields were not taken to output.

Output Fields: Campaign Id, Campaign Label, Delivery Id, Delivery Label, Delivery Log Event Date, Status, Recipient Id, Scv Id

 

Stage-6 : Data extraction

The output of the Stage-5 is written to the file.

Output Fields: Campaign Id, Campaign Label, Delivery Id, Delivery Label, Delivery Log Event Date, Status, Recipient Id, Scv Id

This is working fine.

Now I would like to pull the “Subject” (i.e., Email Subject) from the “Email header parameters” associated with the Delivery schema.

I can see the data item “Subject” and many more when browsed from the Source schema as shown below.

Browsed from the Source schema

Browsed from the Source schema.PNG

But, I can’t see the data item “Subject” and many more when browsed from the Query or Enrich activity as shown below.

Browsed from the Query or Enrichment

Browsed from the Query or Enrichment.PNG

Could you please advise.

Regards,

Mahantesh

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Springboard
MVP
Florian_Courgey
MVP

Likes

93 likes

Total Posts

87 posts

Correct reply

19 solutions
Top badges earned
Springboard
Validate 1
Establish
Contributor
Ignite 1
View profile

Avatar
Springboard
MVP
Florian_Courgey
MVP

Likes

93 likes

Total Posts

87 posts

Correct reply

19 solutions
Top badges earned
Springboard
Validate 1
Establish
Contributor
Ignite 1
View profile
Florian_Courgey
MVP

22-04-2019

Hi,

The subject is stored in the XML field mData which contains a lot of variables. You have to extract mData and get the subject via its XPath.

Workflow:

20190422-1711-screenshot-3.jpg

In your last Enrichment, create an empty column name @subject with '' as a value. This creates an SQL field named sSubject:

20190422-1713-screenshot-4.jpg

Add a Javascript activity between the Enrichment and the File Export, to fill in this new column:

// get data from previous transition

var query = xtk.queryDef.create({queryDef:{

  schema:vars.targetSchema, operation:"select",

  select:{node:[

    {expr:'@broadlogId'},

    {expr:'@deliveryId'},

  ]}

}});

var records = query.ExecuteQuery();

for each(var broadlog in records){ 

  var dataStr = sqlGetMemo("SELECT mData from nmsDelivery WHERE iDeliveryId="+broadlog.@deliveryId); // contains '<mailParameters mirrorPagePolicy="default" useDefaultErrorAddress="true"><subject><![CDA

  dataXml = DOMDocument.fromXMLString(dataStr);

  //var subject = dataXml.getElementsByTagName('mailParameters')[0].getValue('subject'); // equivalent to:

  var subject = dataXml.root.getValue('mailParameters/subject');

  if(subject){ // may be null

    var sql = "UPDATE "+vars.tableName+" SET "+

      "sSubject = '"+subject+"' "+

      "WHERE iId="+broadlog.@broadlogId+";";

    logInfo(sql);

    sqlExec(sql);

  }

}

Display the target now gives us the subject:

20190422-1709-screenshot-2.jpg

PS: for better perfomance, I guess you don't need all those Enrichments. Just one query on BroadLogRcp and get the fields you need (even the "linked" ones to Delivery and Campaign)

Answers (5)

Answers (5)

Avatar

Avatar
Validate 1
Employee
Vapsy
Employee

Likes

370 likes

Total Posts

726 posts

Correct reply

342 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile

Avatar
Validate 1
Employee
Vapsy
Employee

Likes

370 likes

Total Posts

726 posts

Correct reply

342 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile
Vapsy
Employee

18-03-2017

Hi Mahantesh,

Subject is a computed XML field and at database level is stored in column named mdata.

Extracting it using the workflow menu items is not possible.

You can only do so using querydef.

Regards,

Vipul

Avatar

Avatar
Springboard
Level 4
Ssb_94
Level 4

Likes

45 likes

Total Posts

118 posts

Correct reply

3 solutions
Top badges earned
Springboard
Establish
Affirm 3
Contributor 2
Give Back 25
View profile

Avatar
Springboard
Level 4
Ssb_94
Level 4

Likes

45 likes

Total Posts

118 posts

Correct reply

3 solutions
Top badges earned
Springboard
Establish
Affirm 3
Contributor 2
Give Back 25
View profile
Ssb_94
Level 4

16-05-2019

Hi Vipul Raghav

I too have similar requirement, where instead of Subject I need to fetch Delivery Content. I tried to use the same way, but facing numerous errors

It is present under delivery/content/html/source. Can you please help me how can i achieve this using queryDef? i don't have any enrichment activity or anything else, i just want to see response in the JS for my further processing

Regards,

Sri Bhargav

Avatar

Avatar
Validate 1
Level 2
nadeema90078524
Level 2

Likes

4 likes

Total Posts

16 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
Affirm 1
View profile

Avatar
Validate 1
Level 2
nadeema90078524
Level 2

Likes

4 likes

Total Posts

16 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
Affirm 1
View profile
nadeema90078524
Level 2

16-07-2018

I am creating a Cube on Tracking Log where email Subject is one such Dimension. How can i do that ? Do i need to use Query Def ?As its a Xml Type atribute.

Avatar

Avatar
Validate 1
Level 2
nadeema90078524
Level 2

Likes

4 likes

Total Posts

16 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
Affirm 1
View profile

Avatar
Validate 1
Level 2
nadeema90078524
Level 2

Likes

4 likes

Total Posts

16 posts

Correct reply

1 solution
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
Affirm 1
View profile
nadeema90078524
Level 2

16-07-2018

m

Avatar

Avatar
Validate 1
Level 1
MeMonty
Level 1

Likes

4 likes

Total Posts

9 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
View profile

Avatar
Validate 1
Level 1
MeMonty
Level 1

Likes

4 likes

Total Posts

9 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 3
Boost 1
Applaud 5
View profile
MeMonty
Level 1

20-03-2017

Hi Vipul,

Thanks for your response.

Could you please guide me, where (at what stage) & how can I achieve this in my workflow (explained in my initial post)?

Regards,

Mahantesh