Unable to use/query against DM delivery output file names

Description

One of my customer is reported the following issue since they have some different business requirement.

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

I have first tried to reach out to my colleagues but unfortunately they don’t have much information to the above question. Since, we know that mdata is an XML field and not a DB field to query upon it. But, apart from that is there any method to achieve this scenario?

Resolution

Use a file extract activity to extract the delivery data in XML format.

This will ensure that the mdata is also extracted.

Then you can use a load file activity or JS code activity to parse that file and get your data.

OR

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 for it?”

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

On this page