Calculated fields using XML memo data
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 -->
