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

[salvdangelo]

Hi,

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

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

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​

[Jean-Serge_Biro]

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

[salvdangelo]

Hi Jean-Serge,

Thank you for being responsive.

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

inside an export template?

Salvatore

[Jean-Serge_Biro]

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:

BR
JS

[Jean-Serge_Biro]

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

[salvdangelo]

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

[Jean-Serge_Biro]

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

[salvdangelo]

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

[Jean-Serge_Biro]

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

[Amit_Kumar]

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

[ckumari12]

Hello Amit_Kumar​

I was trying above suggested solution. Although I am able to add subject field in the output column of query activity, it is not giving me the result.

Please see screenshot.

How can I extract the values in a file?

Thanks,

Chandni

[szymons55769873]

Hi All

It is often indicated that whatever workaround will be used to extract/insert subject line there is still a problem of that subject line being BEFORE personalization (so any dynamic parameters in subject will be there as plain text). 

Has anybody figured out a way to obtain the subject that is AFTER personalization step and has all the dynamic parameters changed to actual DB values?

@Jean-Serge_Biro  @Amit_Kumar