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

[david--garcia]

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 

[david--garcia · Accepted solution]

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

[chandra_gundreddy]

Thanks for post , was helpful to me