How to Iterate nodes in XML response

[Mrman]

Hi All,

I'm looking for some help pulling some data from an XML document that is grabbed via a SOAP API .

The structure of the XML document is as follows:

I am trying to get the Monthly salary(MON) and Annual Base salary (ANN) where the <frequency> is mentioned as ANN or MON .

I should iterate through all the <paycompensation_recurring> nodes and based on Frequency and End date I should get the Value of <paycompvalue> which is the amount . 

Please let me know how to iterate all the <paycompensation_recurring> nodes and when my condition matches based on frequency and enddate I wan to get the value.

Below is the Source script i Created as part of Transform map and this is only getting value of first instance of <paycompensation_recurring> . It is not going to all nodes.

answer = (function getValueForTarget(request, response) {

    var PAY_FREQ_PATH = "//person/employment_information/compensation_information/paycompensation_recurring/frequency";
    var PAY_VALUE_PATH = "//person/employment_information/compensation_information/paycompensation_recurring/paycompvalue";

    var requestData = JSON.parse(request);
    var xmlDoc = new XMLDocument2();
    xmlDoc.parseXML(response);
    var freqID = xmlDoc.getNode(PAY_FREQ_PATH).getTextContent();
    var payvalueID = xmlDoc.getNode(PAY_VALUE_PATH).getTextContent();
    
    if (freqID == 'MON' && payvalueID.toString().trim() != "") {
        
        return payvalueID;
    }
})(request, response);

<person>
                        
                        <employment_information>
                            
                            <compensation_information>
                                                                                             
                                <end_date>9999-12-31</end_date>
                                <event>12</event>
                                                               
                                <paycompensation_recurring>
                                    <annualizationFactor>13.0</annualizationFactor>
                                    
                                    <currency_code>BRL</currency_code>
                                    <end_date>9999-12-31</end_date>
                                    <frequency>M13</frequency>
                                    <is_target>false</is_target>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>1793.17</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2017-11-01</start_date>
                                </paycompensation_recurring>
                                <paycompensation_recurring>
                                    <annualizationFactor>1.0</annualizationFactor>
                                    
                                    <currency_code>BRL</currency_code>
                                    <end_date>9999-12-31</end_date>
                                    <frequency>ANN</frequency>
                                    <is_target>false</is_target>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>23311.21</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2017-11-01</start_date>
                                </paycompensation_recurring>
                            </compensation_information>
                            <compensation_information>
                                
                                <custom_double5>1632.59</custom_double5>
                                <end_date>2017-10-31</end_date>
                                <event>12</event>
                                <event_reason>PAYEQT</event_reason>
                                <is_eligible_for_benefits>false</is_eligible_for_benefits>
                                <is_eligible_for_car>false</is_eligible_for_car>
                                
                                <seq_number>1</seq_number>
                                <start_date>2016-11-01</start_date>
                                <paycompensation_recurring>
                                    <annualizationFactor>13.0</annualizationFactor>
                                    <created_by>09128827</created_by>
                                    
                                    <currency_code>BRL</currency_code>
                                    <end_date>2017-10-31</end_date>
                                    <frequency>M13</frequency>
                                    <is_target>false</is_target>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>1763.2</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2016-11-01</start_date>
                                </paycompensation_recurring>
                                <paycompensation_recurring>
                                    <annualizationFactor>1.0</annualizationFactor>
                                    
                                    <currency_code>BRL</currency_code>
                                    <end_date>2017-10-31</end_date>
                                    <frequency>ANN</frequency>
                                    <is_target>false</is_target>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>22921.56</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2016-11-01</start_date>
                                </paycompensation_recurring>
                            </compensation_information>
                            <compensation_information>
                                
                                <custom_double5>1419.05</custom_double5>
                                <end_date>2016-10-31</end_date>
                                <event>12</event>
                                <event_reason>PAYEQT</event_reason>
                                <is_eligible_for_benefits>false</is_eligible_for_benefits>
                                <is_eligible_for_car>false</is_eligible_for_car>
                                
                                <seq_number>1</seq_number>
                                <start_date>2016-09-01</start_date>
                                <paycompensation_recurring>
                                    <annualizationFactor>13.0</annualizationFactor>
                                                                      
                                    <currency_code>BRL</currency_code>
                                    <end_date>2016-10-31</end_date>
                                    <frequency>M13</frequency>
                                    <is_target>false</is_target>
                                    <last_modified_by>09128827</last_modified_by>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>1632.59</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2016-09-01</start_date>
                                </paycompensation_recurring>
                                <paycompensation_recurring>
                                    <annualizationFactor>1.0</annualizationFactor>
                                  
                                    <currency_code>BRL</currency_code>
                                    <end_date>2016-10-31</end_date>
                                    <frequency>ANN</frequency>
                                    <is_target>false</is_target>
                                    
                                    <pay_component>PC</pay_component>
                                    <pay_component_type>AMOUNT</pay_component_type>
                                    <paycompvalue>21223.67</paycompvalue>
                                    <seq_number>1</seq_number>
                                    <start_date>2016-09-01</start_date>
                                </paycompensation_recurring>
                            </compensation_information>
</employment_information>
                    </person>

[Maik Skoddow]

Hi @ramamr 

see https://developer.servicenow.com/dev.do#!/reference/api/orlando/server/no-namespace/c_XMLNodeIterato... for an iterator example.

Kind regards
Maik

If my answer replied your question please mark appropriate response as correct so that the question will appear as resolved for other users who may have a similar question in the future.

[Maik Skoddow]

Hi @ramamr 

Did my reply answer your question?

If so, please mark appropriate response as correct so that the question will appear as resolved for other users who may have a similar question in the future.

If not, please tell me what you are still missing!

Kind regards
Maik

[Mrman]

@Maik Skoddow I have to the link you provided and tried , but still I am not able to iterate through all frequencies and paycomponents .. could you guide.

[Maik Skoddow · Accepted solution]

Hi @ramamr 

a working example looks like this:

var requestData = JSON.parse(request);
var xmlDoc = new XMLDocument2();

xmlDoc.parseXML(response);

var paycompensationRecurringNode = xmlDoc.getFirstNode("//paycompensation_recurring");

while(paycompensationRecurringNode != null) {
  var nodeValues    = {};
  var childIterator = paycompensationRecurringNode.getChildNodeIterator();
    
  while (childIterator.hasNext()) {
    var childNode      = childIterator.next();
    var childNodeName  = childNode.getNodeName();
    var childNodeValue = childNode.getTextContent();
    
    nodeValues[childNodeName] = childNodeValue;
  }
  
  
  gs.info(nodeValues.pay_component);
  gs.info(nodeValues.annualizationFactor);
  
  paycompensationRecurringNode = xmlDoc.getNextNode(paycompensationRecurringNode);
}

At object nodeValues you will find the values of the child nodes of each paycompensation_recurring node.

Kind regards
Maik

If my answer replied your question please mark appropriate response as correct so that the question will appear as resolved for other users who may have a similar question in the future.