CVP Call studio - read variables from xml_resultset

[piyush aghera]

Hello,

I am developing a CVP call studio (V 11.6) app to read data from MS SQL DB and send email for each row. This app is integrated with MS SQL DB and I am getting multiple rows in select statement.  Till this point, everything is fine, I get results in xml_resultset field when I run that application in debug mode. 

But now I am struggling to take individual fields / data from this xml_resultset and save them in variables(element, session or local variables, anything is fine) to use them further in the application and trigger email for each row which is returned.

Session Data Key field of Database element gets below value:

com.audium.server.action.database.ResultSetList@7f67ac59

My sample xml_resultset is as below:

<?xml version="1.0" encoding="UTF-8" standalone="no"?><Results><Row><ToEmail>john1@abccorp.com</ToEmail><FromEmail>admin@abccorp.com</FromEmail><FirstName>John</FirstName><LastName>Doe</LastName><Amount>250</Amount><DueDate>2020-09-12</DueDate><EmailType>Registration Confirmation</EmailType></Row><Row><ToEmail>jane1@abccorp.com</ToEmail><FromEmail>admin@abccorp.com</FromEmail><FirstName>Jane</FirstName><LastName>Doe</LastName><Amount>350</Amount><DueDate>2020-09-11</DueDate><EmailType>Registration Confirmation</EmailType></Row></Results>

Any pointers would be much appreciated.

[Marek (gaman-gt.com) · Accepted solution]

Hi @piyush aghera 

I think that you are CVP JavaScript Utilities is the feature that you are looking for. Here is a Cisco documentation that describes it: CVP Documentation . I think that this was introduced in CVP 11.5. Please look at XPath section of the document which is designed to parse data from XML documents using XPath.

Under this link you will find some info how to use the XPath query: Link 

Because you have multiple entries, probably you will need to build a loop in the CVP script (first determinate the number of entries in the XML using "count" function, and then iterate through elements).

If that will not fit your case, then the option is to consider building a custom CVP element that will automatically split the output data into session variable (or maybe an array that can be accessed via index).

Which approach to use - it's your decision.

[piyush aghera]

Thanks for detailed information. I will go thru these. Meanwhile I found an old post of Jenine who described similar steps which worked for me.

Appreciate your time and efforts.

[ptindall]

I see you've opted for the JavaScript approach.  Just for reference, I've attached a custom element for anyone who wants to extract data from the multi-select results set without XML / XPath.   You can get row count or retrieve a list of named columns for any given row.

[piyush aghera]

Re-posting above mentioned post:

https://community.cisco.com/t5/contact-center/retrive-information-from-database-element/td-p/3531488