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

How to read mData data memo field xpath, javascript sqlGetMemo fromXMLString DOMDocument

David__Garcia
Employee
Employee

I am using the data/mData field in the recipient schema to store some data which has the following structure.

 

 

 

<?xml version="1.0"?>
<recipient>
	<comment></comment>
	<changes firstName="David" lastName="Garcia" email="davidgarcia@xxx.xxx" emailPreferredName="Dave" JOB_TITLE="xxx" company="xxx" blackListEmail="0" lawfulBasis="3"></changes>
</recipient>

 

 

Here is my script to write the data above to the recipient schema

 

 

      /** store tmp changes in xml field **/
      xmlData = '<?xml version="1.0"?>'
               +'<recipient>'
               +'<comment></comment>'
               +'<changes firstName="'+ctx.recipient.@firstName.toString()+'" lastName="'+ctx.recipient.@lastName.toString()+'" email="'+ctx.recipient.@email.toString()+'" emailPreferredName="'+ctx.recipient.@emailPreferredName.toString()+'" JOB_TITLE="'+ctx.recipient.@JOB_TITLE.toString()+'" company="'+ctx.recipient.@company+'" blackListEmail="'+ctx.recipient.@blackListEmail+'" lawfulBasis="'+ctx.recipient.@lawfulBasis+'"></changes>'
               +'</recipient>'   
               
       sqlExec("UPDATE  NmsRecipient SET mData = '"+xmlData+"' WHERE iRecipientId='"+parseInt(ctx.recipient.@id)+"'  "); 

 

 

 

Now I want to read the data stored by converting the mData to DOMdocument so that I can use xpath to traverse through the nodes and get the values to assign them to variables.

 

 

var recipientId = 13241267;
var x = sqlGetMemo('SELECT mData FROM nmsRecipient WHERE iRecipientId ='+recipientId);
var y = DOMDocument.fromXMLString(x); // convert to DOMDocument
//var z = y.root.getValue('recipient.changes.@firstName'); doesnt work either
var z = y.getElementsByTagName('changes')[0].getValue('firstName');
logInfo(z)

 

 

 End goal is to retrieve each value from specified tags so that I can assing them  to variables to perform a sql update.

 

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

1 Accepted Solution
David__Garcia
Correct answer by
Employee
Employee

After a lot of testing trial and error, the following worked; it was the xpath was wrong.

 

  var recipientId = 13241267;
  var x  = sqlGetMemo('SELECT mData FROM nmsRecipient WHERE iRecipientId ='+recipientId);
  var y  = DOMDocument.fromXMLString(x);
  var z  = y.root.getValue('/changes/@firstName'); //now works
  var za = y.getElementsByTagName('recipient')[0].getValue('/changes/@firstName'); //now works

View solution in original post

1 Reply
David__Garcia
Correct answer by
Employee
Employee

After a lot of testing trial and error, the following worked; it was the xpath was wrong.

 

  var recipientId = 13241267;
  var x  = sqlGetMemo('SELECT mData FROM nmsRecipient WHERE iRecipientId ='+recipientId);
  var y  = DOMDocument.fromXMLString(x);
  var z  = y.root.getValue('/changes/@firstName'); //now works
  var za = y.getElementsByTagName('recipient')[0].getValue('/changes/@firstName'); //now works