Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Calculated fields using XML memo data

Avatar

Employee Advisor

Given that I have the following recipient mData/data structure

David__Garcia_0-1640320469310.png

 

 

 

 

<?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 -->

 

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

 

 

View solution in original post

1 Reply

Avatar

Correct answer by
Community Advisor

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