Outbound REST API GET from XML Response to Table

[dylanlevy]

So I have an Outbound REST API GET setup correctly and the test is pulling the data back correctly. I am trying to pull (using a GET) CIs from a third party tool into my cmdb (Access Points to a new Access Point table that extends from the Network Gear table)

It is pulling all the devices with the information needed, but I need help getting those devices into the new table. How should I go about doing this? This needs to happen on a reoccurring basis, so something scheduled would be preferred. I want to split out each entity to be its own record, making one of the fields the unique field. 

Here is an example of the xml response:

<?xml version="1.0" ?>
<queryResponse last="99" first="0" count="455" type="AccessPoints" responseType="listEntityInstances" requestUrl="" rootUrl="">
  <entity dtoType="accessPointsDTO" type="AccessPoints" url="">
    <accessPointsDTO displayName="693695" id="693695">
      <adminStatus>ENABLE</adminStatus>
      <bootVersion>1.1.2.4</bootVersion>
      <clientCount>0</clientCount>
      <clientCount_2_4GHz>0</clientCount_2_4GHz>
      <clientCount_5GHz>0</clientCount_5GHz>
      <controllerIpAddress>10.1.5.130</controllerIpAddress>
      <controllerName></controllerName>
      <countryCode></countryCode>
      <ethernetMac></ethernetMac>
      <hreapEnabled>false</hreapEnabled>
      <ipAddress></ipAddress>
      <location>default location</location>
      <lwappUpTime>170020453</lwappUpTime>
      <macAddress>/macAddress>
      <model></model>
      <name></name>
      <serialNumber></serialNumber>
      <softwareVersion></softwareVersion>
      <status></status>
      <type></type>
      <upTime></upTime>
    </accessPointsDTO>
  </entity>
  <entity dtoType="accessPointsDTO" type="AccessPoints" url="">
    <accessPointsDTO displayName="693700" id="693700">
      <adminStatus>ENABLE</adminStatus>
      <bootVersion>15.2.4.5</bootVersion>
      <clientCount>0</clientCount>
      <clientCount_2_4GHz>0</clientCount_2_4GHz>
      <clientCount_5GHz>0</clientCount_5GHz>
      <controllerIpAddress></controllerIpAddress>
      <controllerName></controllerName>
      <countryCode>US</countryCode>
      <ethernetMac></ethernetMac>
      <hreapEnabled>false</hreapEnabled>
      <ipAddress></ipAddress>
      <location>Store</location>
      <lwappUpTime>1538746054</lwappUpTime>
      <macAddress></macAddress>
      <model></model>
      <name></name>
      <serialNumber></serialNumber>
      <softwareVersion></softwareVersion>
      <status></status>
      <type></type>
      <upTime></upTime>
    </accessPointsDTO>
  </entity>

[Ankur Bawiskar]

Hi Dylan,

Once you are getting the response from API. parse the xml response and fetch the values and insert in whatever table you need.

If you are using scoped app then xml parsing is different than global scope.

Mark Correct if this solves your issue and also hit Like and Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[dylanlevy]

I know what I need to do, but the question is HOW do I do it. I have no idea how to parse the xml response to get the information I need from it. 

[Chris Sanford1]

This code should get you started. I also assumed there is a '</queryResponse>' tag at the end because as is you would have invalid xml. Replace xmlString with your response body, and add some GlideRecord calls in your business rule within the nested while loop to insert/update values with whatever field you want to use as a key. I just tested this in a background script and it printed expected results:

var xmlString ='<?xml version="1.0" ?>' +
'<queryResponse last="99" first="0" count="455" type="AccessPoints" responseType="listEntityInstances" requestUrl="" rootUrl="">' +
  '<entity dtoType="accessPointsDTO" type="AccessPoints" url="">' +
    '<accessPointsDTO displayName="693695" id="693695">' +
      '<adminStatus>ENABLE</adminStatus>' +
      '<bootVersion>1.1.2.4</bootVersion>' +
      '<clientCount>0</clientCount>' +
      '<clientCount_2_4GHz>0</clientCount_2_4GHz>' +
      '<clientCount_5GHz>0</clientCount_5GHz>' +
      '<controllerIpAddress>10.1.5.130</controllerIpAddress>' +
      '<controllerName></controllerName>' +
      '<countryCode></countryCode>' +
      '<ethernetMac></ethernetMac>' +
      '<hreapEnabled>false</hreapEnabled>' +
      '<ipAddress></ipAddress>' +
      '<location>default location</location>' +
      '<lwappUpTime>170020453</lwappUpTime>' +
      '<macAddress></macAddress>' +
      '<model></model>' +
      '<name></name>' +
      '<serialNumber></serialNumber>' +
      '<softwareVersion></softwareVersion>' +
      '<status></status>' +
      '<type></type>' +
      '<upTime></upTime>' +
    '</accessPointsDTO>' +
  '</entity>' +
  '<entity dtoType="accessPointsDTO" type="AccessPoints" url="">' +
    '<accessPointsDTO displayName="693700" id="693700">' +
      '<adminStatus>ENABLE</adminStatus>' +
      '<bootVersion>15.2.4.5</bootVersion>' +
      '<clientCount>0</clientCount>' +
      '<clientCount_2_4GHz>0</clientCount_2_4GHz>' +
      '<clientCount_5GHz>0</clientCount_5GHz>' +
      '<controllerIpAddress></controllerIpAddress>' +
      '<controllerName></controllerName>' +
      '<countryCode>US</countryCode>' +
      '<ethernetMac></ethernetMac>' +
      '<hreapEnabled>false</hreapEnabled>' +
      '<ipAddress></ipAddress>' +
      '<location>Store</location>' +
      '<lwappUpTime>1538746054</lwappUpTime>' +
      '<macAddress></macAddress>' +
      '<model></model>' +
      '<name></name>' +
      '<serialNumber></serialNumber>' +
      '<softwareVersion></softwareVersion>' +
      '<status></status>' +
      '<type></type>' +
      '<upTime></upTime>' +
    '</accessPointsDTO>' +
  '</entity>' +
'</queryResponse>';

var xmldoc = new XMLDocument2();
xmldoc.parseXML(xmlString);
var node = xmldoc.getNode('/queryResponse');

var i = node.getChildNodeIterator();
while(i.hasNext()) {
   var entity = i.next().getChildNodeIterator();
   var j = entity.next().getChildNodeIterator();
   while(j.hasNext()) {
      var element = j.next();
      var fieldName = element.getNodeName();
      var fieldValue = element.getTextContent();
      gs.info(fieldName + ': ' + fieldValue);
   }
}

[dylanlevy]

Weird - I'm getting this error. I'm also going to attach the full response I'm seeing. Maybe that will help. The difference in how I'm running the script is that I'm actually calling the API.  

"java.lang.ClassCastException: org.apache.axiom.om.impl.dom.TextImpl cannot be cast to org.apache.axiom.om.impl.dom.ParentNode"