Unable to use/query against DM delivery output file names
This article outlines a solution for extracting output file names from the mdata field in Campaign Classic and Campaign Standard environments. To achieve this, you can either employ the file extraction activity or JS code activity with queryDef to fetch the mdata of your delivery and parse the XML. A specific example is provided for the latter method.
Description description
Environment
- Campaign Classic
- Campaign
- Campaign Standard
Issue/Symptoms
From mdata field, you want to extract the outputFile name–see attachment(mData_outputFile.png) for location, stored within outputFile=" ". You 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 you can append into your promotion history table to store all the output file names.
Since mdata is an XML field and not a DB field to query upon it, is there any method to achieve this scenario?
Resolution resolution
You can try to resolve this issue in two ways:
Scenario 1:
- Use a file extract activity to extract the delivery data in XML format. This ensures that the
mdatais also extracted. After that, you can use a load file activity or JavaScript (JS code) activity to parse that file and get your data. - Use a JS code activity and
queryDefto fetchmdataof your delivery and then parse the XML. Store data in an instance variable to then load in transition.
Scenario 2:
You are trying to use the method of queryDef against mdata, which you were able to write. For the parse method, what is the recommended approach?
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>