How to read mData data memo field xpath, javascript sqlGetMemo fromXMLString DOMDocument | Community
Skip to main content
david--garcia
david--garcia
Level 10
December 11, 2021
Solved

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

  • December 11, 2021
  • 1 reply
  • 1734 views

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 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by david--garcia

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

1 reply

david--garcia
david--garciaAuthorAccepted solution
Level 10
December 11, 2021

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
    C
    chandra_gundreddy
    April 5, 2023

    Thanks for post , was helpful to me

      Terms & ConditionsAccessibility statement

      Loading footer...