As explained in this ticket (Exporting XML Fields ), it is not possible to use XML field (memo) directly from:
For instance, in your case, you can use:
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.
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:
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.
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.
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?
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
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.
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).
If anyone else knows a better solution, please share your knowledge.
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
<node analyze="true" expr="[mailParameters/subject]" label="subject"/>
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.