Unable to use/query against DM delivery output file names

Description description

Environment

  • Campaign Classic
  • Campaign
  • Campaign Standard

Issue/Symptoms
One of the customers has reported the following issue:

From mdata field, we want to extract the outputFile name–see attachment(mData_outputFile.png) for location, stored within outputFile=" ". We want to know the best way to transform this XML data type field to be passed as an enriched field within the campaign workflow. So that we can append into our promotion history table to store all the output file names.

Since, we know that mdata is an XML field and not a DB field to query upon it. But is there any method to achieve this scenario?

Resolution resolution

We can try to resolve this issue in two ways:

  1. Use a file extract activity to extract the delivery data in XML format. This will ensure that the mdata is also extracted. After that, you can use a load file activity or JavaScript (JS code) activity to parse that file and get your data.
  2. Use a JS code activity and queryDef to fetch mdata of your delivery and then parse the XML. Store data in an instance variable to then load in transition

Customer had a follow up query which was as below:

We are trying to use the method of queryDef against mdata, which we were able to write. For the parse method, what is the recommended approach?

Answer:

Your request will be like:

queryDef operation=“select” schema=“nms:delivery”

select

node expr=“@id”/

node expr=“output/@fileName” label=“Output file”/

/select

sysFilter

condition expr=“@isModel = 0”/

/sysFilter

orderBy

node expr=“@lastModified” sortDesc=“true”/

/orderBy

/queryDef

And it will return:

delivery-collection

delivery id=“11878415”

output filename=”abc.txt”

/output

/delivery

/delivery-collection

recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f