Calculated fields using XML memo data

[david--garcia]

Given that I have the following recipient mData/data structure

<?xml version='1.0'?>
<recipient>
	<comment></comment>
	<changes changeDate="2021/12/24 21:58:45"/>
</recipient>

I tried using the following expression in a calculated field and in queryDef as well as [changes/@changeDate] in the where clause to no avail, I have a timestamp stored in the mData field and I need to calculate which records are over 72 hours old.

"Iif(Abs([changes/@changeDate] - GetDate()) / 36e5) >= 72, 'Expired','Pending'"

The following error is generated

24/12/2021 04:51:43 js6 QUE-370015 The node of path '/changes/@changeDate' is not stored as an independent SQL field (document of type 'Recipients (nms:recipient)'). Unable to use it as a filtering criteria in a query.

Is it definitely not possible to query the xml field using soap methods?

To get around the issue I had to resort to the following script which is a bit of an overkill, If i could just create a calculated field to pull the timestamp from an xml field that would be great, anyone know any easier alternatives?

var query = xtk.queryDef.create(  
  <queryDef schema="nms:recipient" operation="select">    
    <select>      
      <node expr="@id"/>            
      <node expr="[changes/@changeDate]" alias="@changeDate"/>                   
    </select>     
    <where>      
      <condition expr="[data] like '%changes%'"/> 
    </where>      
  </queryDef>)

var res = query.ExecuteQuery()

c = 0
const expired = []
for each (var i in res) {
    
   try {
   var expiry = Math.abs(new Date(Date.parse(i.@changeDate)) - new Date()) / 36e5;

   if (expiry >= 72) {   
    c++ 
     expired.push(i.@id) //store expired records in object
     
     /*remove tmp data, keep comments if any*/
       var rcpObj = nms.recipient.load(i.@id);
              
       if (rcpObj.comment.length > 0) {        
           xmlData = '<?xml version="1.0"?><recipient><comment>'+rcpObj.comment+'</comment></recipient>'       
        } else {var xmlData = ''}   
       
     sqlExec("UPDATE NmsRecipient SET mData = '"+xmlData+"' WHERE iRecipientId='"+parseInt(i.@id)+"'");                  
     /*              end                    */
   
   } else if(expiry < 72) {
     logInfo(i.@id+":"+expiry) //non expired records
   } else {
   logInfo(i.@id+":"+expiry) //non compliant records
   }
       }catch(e) {logInfo(e)}
}

logWarning("Expired: "+c+": "+expired)

Here is my schema extension for the [changes] element in recipient schema.

    <!--tmp recipient changes 18122021 David Garcia-->
    <element label="changes" name="changes" xml="true">
      <attribute label="tmp firstName" name="firstName" type="string" xml="true"/>
      <attribute label="tmp LastName" name="lastName" type="string" xml="true"/>
      <attribute label="tmp email" name="email" type="string" xml="true"/>
      <attribute label="tmp emailPreferredName" name="emailPreferredName" type="string"
                 xml="true"/>
      <attribute label="tmp JOB_TITLE" name="JOB_TITLE" type="string" xml="true"/>
      <attribute label="tmp company" name="company" type="string" xml="true"/>
      <attribute label="tmp blackListEmail" name="blackListEmail" type="string" xml="true"/>
      <attribute label="tmp lawfulBasis" name="lawfulBasis" type="string" xml="true"/>
      <attribute label="tmp changeDate" name="changeDate" type="datetime" xml="true"/>
    </element>
    <!--tmp recipient changes -->

[david--garcia]

@Darren_Bowers @LaurentLam @Marcel_Szimonisz @Adhiyan @Jonathon_wodnicki @CedricRey @Milan_Vucetic @Manoj_Kumar_ @Florian_Courgey @Jean-Serge_Biro

[Jyoti_Yadav · Accepted solution]

Hi @david--garcia ,

1) Try using below syntax to fetch the value of 'changeDate':

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

Once you save 'changeDate' value, then you can use another enrichment activity to use your code.

2) Or use complete code in one syntax:

Iif(Abs(((Substring(data, Charindex(data, 'changeDate='), 19)) - GetDate()) / 36e5) >= 72, 'Expired','Pending')

Thanks,

Jyoti

[david--garcia]

Thanks!