Highlighted

Export 'subject' (or in general XML field) in export template

salvdangelo

23-10-2017

Hi,

How should I export an XML field through an export job template?

In particular regarding 'delivery subject' ([delivery/mailParameters/subject])

Capture.JPG

I read a similar thread, but that wasn't clear for me.

Any solution?


Salvatore

Jean-Serge Biron

Amit_Kumar

Vipul Raghav

florentlb

Adobe Campaign

Replies

Highlighted

Jean-Serge_Biro

MVP

23-10-2017

Hello Salvatore,

As explained in this ticket (Exporting XML Fields ), it is not possible to use XML field (memo) directly from:

  • Query, Enrichment, Export activities in a workflow (either extract field or where criteria field)
  • queryDef node field or where criteria function in a Javascript activity

But you can manipulate the XML field in Javascript, so this is always a valuable workaround.

For instance, in your case, you can use:
message.delivery.mailParameters.subject

to get the delivery subject content.

But take care, the subject content in the delivery contains common data for all the messages sent to contact; so in case of personalizing the subject, you won't get the exact values per contact message but the personalisation code as raw data, as explained in the ticket indicated above.

Regards.
J-Serge

Highlighted

salvdangelo

23-10-2017

Hi Jean-Serge,

Thank you for being responsive.

How may I use this JS code (message.delivery.mailParameters.subject)

inside an export template?

Salvatore

         
Highlighted

Jean-Serge_Biro

MVP

23-10-2017

Sorry, Salvatore, I was misleading you, I thought you was using a workflow Extract activity, not an export template.

It is possible from an export template to get out XML field directly.

So if you are targeting delivery dimension, please set your extract field as below:

1331041_pastedImage_0.png

BR
JS

Highlighted

Jean-Serge_Biro

MVP

23-10-2017

And also I must mention that I was wrong, you can select for extracting the XML field (target/mailParameters/subject) from an Extract activity.

So it remains not possible from queryDef and Query/Enrichment activity only.

Regards
JS

Highlighted

salvdangelo

23-10-2017

This is exactly how I was doing.
What I cannot understand is why extraction WITHOUT the subject takes about 8 minutes, WITH the subject about 9+ HOURS.
And I don't know if the extraction works fine because I never completed it.

Thanks anyway,

Salvatore

Highlighted

Jean-Serge_Biro

MVP

23-10-2017

I guess that the performance response time issue is probably due to the target dimension that you use (broadLogRcp schema, according to your first screenshot) and the internal algorithm used by Export template activity.

With the join done to delivery schema, in order to get the subject, even if it is common to all messages (broadlogs) for one delivery, perhaps there is something wrong in the underlying SQL execution plan made by the export template algorithm.

I did a test on my own environment, and with a few normal deliveries/large number of broadlog, 1,6 M broadlogs records, you are right, it would take too much time, I had to stop the job execution.


With 3500 lines of broadLogRcp (very few selected only, for this test), it takes less than 2 minutes.

Please may you share your volumetry ?

And please may you test performance with worflow and Extract activity as an alternative way?

Regards
J-Serge

Highlighted

salvdangelo

23-10-2017

2,4 M of records for broadLogRcp

However, I don't know how should I test the performance with the workflow since the query cannot extract the XML field.
I suppose you are suggesting to use the JS code you were talking about but I don't know how to implement it in this moment


Salvatore

Highlighted

Jean-Serge_Biro

MVP

23-10-2017

I confirm the issue:

it takes too much time than necessary with Export template tool; even if subject is a shared information of all messages of a delivery, the operation is probably bad managed by the Export template tool, probably because of the mData field management itself.


For my "small" servers (Windows 8 Go RAM for AC applicative server, and SQL Server medium server), I get 266.000 records exported in 50 minutes with Export template tool.

But I also confirm the same performance issue the workflow Extract activity method:

I get the same records export done in 14 minutes
Better than Export template but too much intensive calculation on applicative server, probably.

(the Query activity itself, to get the broadlog address and the delivery subject takes only a few seconds.)

However, with only broadLogRcp and delivery internalName, the whole export task is completed in a few seconds.

I have tried by putting the Javascript code in the Extract activity format section (adding an element) but the response time is not better (well, Javascript is not recommended for a large set but I did it for standard fields on millions of line and it is pretty quick, compared to this small set of lines with memo type field...).

Unfortunately this can't be achieved by retrieving the subject information from a Query activity (or Enrichment, or Change axis activities as I described in my 1st answer, due to XML type of this field).

The only way I find would be in Javascript activity within a workflow, to "merge" the queryDef for broadLogRcp and the Javascript manipulation of XML objects.

If anyone else knows a better solution, please share your knowledge.

Regards
JS


Highlighted

Amit_Kumar

MVP

12-07-2018

Hi Jean-Serge Biron,

I have updated the query activity to allow analyze="true" attribute in the output columns inside the select command.

in the end query looks like this

<select>

        <node analyze="true" expr="[mailParameters/subject]" label="subject"/>

    </select>

Note: for temporary purpose you can simply edit the xml of the query activity and change analyse= false to true.

this is pretty fast and you can export the data. the only downside that you will be getting subject like <subject><![CDATA[test email]]></subject>.

which I am using substring to get the exact value in the following enrichment.

it took me 15 seconds to get this for 1 million records.

Hope this helps.

Regards,

Amit