How to read a XML stored in a variable as memo data type (from schema) and pass its content to Personalisation block?

[AsmitaSingh]

Hello,

I am trying to create a table with dynamic rows & columns via a personalisation block.
I have XML stored in a memo data type variable in schema.

The problem is I am not able to read the xml in Personalisation block as its not part of targetData.It always give me empty value.

I have tried to add to target Data in the workflow but if I assign it to some variable via sesssion.write, gives me error on string length (obvious as 255 char is max). Is there any way I can increase the length or any other method via which I can get it added to target data?

I have also tried using queryDef in Personalisation block which gives me error, 'xtk is undefined'.

My final requirement is to access nodes of XML in Personalisation block.
Any suggestions?

Thanks

[Jyoti_Yadav]

Hi @AsmitaSingh ,

I will suggest you to split you memo Data into targetData variables.

Ex: Suppose below is the value stored in your data:

<recipient>

<email>xyz@xyz.com</email>

<name>abc</name>

</recipient>

Then you can create a variable named 'email1' and assign value as:

Substring(data, Charindex(data, 'email='), 19)

Once you have value in 'email1' field try to trim your data before </email>.

Thanks,

Jyoti

[AsmitaSingh]

Hi @Jyoti_Yadav ,

Thanks for your response.
I am not sure if suggested method will be feasible as the XML is really complex, sharing an example below:

<recipient>

<email>xyz@xyz.com</email>

<name>abc</name>

<location>

          <country> XYZ </country>

          <address>

          <county>abs</county>

          <city>

                    <cityName>xxx</cityName>

                    <postcode>567</postcode>

                    <addressLine1>abc</addressLine1>

                    <addressLine2>nm</addressLine2>

                    <addressLine3>nm</addressLine3>

           </city>
        </address>

</location>

</recipient>

Not the best example though.

Suppose we are recording address record from last 10 years.

Recipients.Location.country can have multiple records depending how many countries person has lived in.

Similarly sub nodes will vary,
Recipients.Location.address.county
Recipients.Location.address.city.postcode etc

Based on the data, I have to generate a table listing the country & other details associated.

	City Name	Post Code	Address Line 1	Address Line 2	Address Line 3
Country 1
Country 2
Country 3
Country 4

PostCode is mandatory but all other columns can be empty/populated. In hypothetical situation a person can have lived in 10 different countries in 10 years or say 5 different countries , so row will vary from 1 to 10.

In original XML, I have 3 dynamic tables similar to above and all have different dynamic content. XML is received via an API call. As its dynamic not able to find best approach to save values in schema. So I have stored entire XML in a memo type variable and now trying to access its elements.

Thanks

[David_Loyd · Accepted solution]

Hey there,

You need to change the format. Data in the memo field is just a string. So when you call that field in a workflow/delivery you need re-define its format/type. See below how the var xmlTrigger calls the memo field to a string then creates a new XML (doc). Once that occurs you can JSON.parse() that XML (if your object needs it). The code below can parse any object level information and write that back to the temp table so you can use it in targetData.

I used the below concepts to parse data from the XML+JSON data stored in ACC memo fields from the Analytics -Pipeline integration.

Here are two examples

XML specific

var res = xtk.queryDef.create (
  <queryDef schema={vars.targetSchema} operation="select" >
    <select>
        <node expr="@data"/> <!-- this is the memo column--/>
        <node expr="@account"/> 
        <node expr="@pageBrand"/>        
    </select>
    <where>
         <condition expr={"[@data] IS NOT NULL" }/>
    </where>
  </queryDef>
).ExecuteQuery();

for each (var msg in res){
  var xmlTrigger = new XML( msg.@data.toString() ); //this is prep
  var enrichments = JSON.parse(xmlTrigger.enrichments.toString()); //this is prep
  
  var pageBrand = enrichments.analytic**bleep**Summary.dimensions.eVar1.data[0]; //map your data
  var prod1 = enrichments.analytic**bleep**Summary.products.cartAdditions.data[0].name; //map your data
  //logInfo("pageBrand: " + pageBrand )  
  var upTempTable = <enrich xtkschema={vars.targetSchema} _operation="update" account={msg.@account} pageBrand={pageBrand} prod1={prod1} _key="@account" />;
   xtk.session.Write(upTempTable);
     
}

Storing JSON in a memo field

var res = xtk.queryDef.create (
  <queryDef schema={vars.targetSchema} operation="select" >
    <select>
        <node expr="@eventPayload"/> <!-- this is my JSON memo field--/>
        <node expr="@account"/>
        <node expr="@question_1"/>
        <node expr="@question_2"/>        
    </select>
    <where>
         <condition expr={"[@eventPayload] IS NOT NULL" }/>
    </where>
  </queryDef>
).ExecuteQuery();

for each (var msg in res){  
  var parseObj = JSON.parse( msg.@eventPayload );
  //map in the details from the JSON responses to use in a workflow
  var ansOne = parseObj.event.responseDetails.selection.answer_1;
  var ansTwo = parseObj.event.responseDetails.selection.answer_1;
      
  var upTempTable = <query xtkschema={vars.targetSchema} _operation="update" _key="@account" account={msg.@account} question_1={ansOne} question_2={ansTwo}  />;
   xtk.session.Write(upTempTable);
     
}

[AsmitaSingh]

Hi @David_Loyd ,

I have tried it already and it works but requirement I have is more complex so have changed the approach.
Thanks for sharing the approach & explaining it

[Sukrity_Wadhwa]

Hi @AsmitaSingh,

It would be quite helpful to the community if you could share the approach here that you have followed. 

Thanks!