Need help parsing a REST API XML response and loading an import set with the result

[edwardhum]

Hi Folks,

I created an outbound rest message to get a list of computers and what to build an import set to load the computers into CMDB.  I can execute the REST call and get the response, the and i've been able to parse the date and get the details i need.  I'm just at loss on how to loop through and create entries in my import set.

sample XML response

<?xml version="1.0" encoding="UTF-8"?>
<computers>
    <size>622</size>
    <computer>
        <id>10</id>
        <name>computer1</name>
        <managed>true</managed>
        <username/>
        <model>MacBookAir7,2</model>
        <department>dept1</department>
        <building>building1</building>
        <mac_address>mac1</mac_address>
        <udid>udid1</udid>
        <serial_number>SERIAL1</serial_number>
        <report_date_utc>2019-06-11T15:29:17.642+0000</report_date_utc>
        <report_date_epoch>1560266957642</report_date_epoch>
    </computer>
    <computer>
        <id>12</id>
        <name>computer2</name>
        <managed>true</managed>
        <username/>
        <model>MacBookAir7,2</model>
        <department>dept1</department>
        <building>building1</building>
        <mac_address>mac2</mac_address>
        <udid>udid2</udid>
        <serial_number>SERIAL2</serial_number>
        <report_date_utc>2019-06-11T22:40:26.702+0000</report_date_utc>
        <report_date_epoch>1560292826702</report_date_epoch>
    </computer>
    <computer>
        <id>19</id>
        <name>computer3</name>
        <managed>true</managed>.............

My script so far.. (in a business rule for testing).  I'd like to move this to a scheduled job.

(function executeRule(current, previous /*null when async*/) {


 var r = new sn_ws.RESTMessageV2('DeviceIntegration', 'get');

//override authentication profile 
//authentication type ='basic'/ 'oauth2'
//r.setAuthenticationProfile(authentication type, profile name);

//set a MID server name if one wants to run the message on MID
//r.setMIDServer('MY_MID_SERVER');

//if the message is configured to communicate through ECC queue, either
//by setting a MID server or calling executeAsync, one needs to set skip_sensor
//to true. Otherwise, one may get an intermittent error that the response body is null
//r.setEccParameter('skip_sensor', true);

	var response = r.execute();
	var responseBody = response.getBody();
	var httpStatus = response.getStatusCode();

	//var jsonString = response.getBody();
	//var parser = new JSONParser();
	//var obj = parser.parse(jsonString);
	//current.description = responseBody;
	var xmlString = response.getBody();
	var xmldoc = new XMLDocument(xmlString,true);
	var r_id = xmldoc.getNodeText("//id");
	var r_name = xmldoc.getNodeText("//name");
	var r_managed = xmldoc.getNodeText("//managed");
	var r_username = xmldoc.getNodeText("//username");
	var r_model = xmldoc.getNodeText("//model");
	var r_department = xmldoc.getNodeText("//department");
	var r_building = xmldoc.getNodeText("//building");
	var r_mac_address = xmldoc.getNodeText("//mac_address");
	var r_udid = xmldoc.getNodeText("//udid");
	var r_serial_number = xmldoc.getNodeText("//serial_number");
	var r_report_date_utc = xmldoc.getNodeText("//report_date_utc");
	var r_report_date_epoch = xmldoc.getNodeText("//report_date_epoch");
	//current.description = r_serial_number;

})(current, previous);

Thanks in advance!

Edward

[Mike Patel]

try using XMLDocument

https://docs.servicenow.com/bundle/jakarta-servicenow-platform/page/script/server-scripting/concept/...

https://docs.servicenow.com/bundle/london-application-development/page/script/server-scripting/conce...

[Mike Patel]

or try converting response from xml to json

https://developer.servicenow.com/app.do#!/api_doc?v=madrid&id=r_SGSYS-xmlToJSON_S

[edwardhum]

Thanks Mike  I've looked at this links quite a bit.  But i'm trying to figure out how to loop through all the Computer entries and insert them into my import set.

Thoughts?

[edwardhum]

Ok, i'm getting closer.....  I've got it to create the import set and loop through, however, it is creating 622 entries of the first computer in the response, not a entry for each computer instance as i was hoping.

Any thoughts?  

Thanks in Advance!

(function executeRule(current, previous /*null when async*/) {

 var r = new sn_ws.RESTMessageV2('DeviceIntegration', 'get');

//override authentication profile 
//authentication type ='basic'/ 'oauth2'
//r.setAuthenticationProfile(authentication type, profile name);

//set a MID server name if one wants to run the message on MID
//r.setMIDServer('MY_MID_SERVER');

//if the message is configured to communicate through ECC queue, either
//by setting a MID server or calling executeAsync, one needs to set skip_sensor
//to true. Otherwise, one may get an intermittent error that the response body is null
//r.setEccParameter('skip_sensor', true);

	var response = r.execute();
	var responseBody = response.getBody();
	var httpStatus = response.getStatusCode();

	var crImpSet = new GlideRecord('sys_import_set');
	crImpSet.initialize();
	crImpSet.mode = 'synchronous';
	crImpSet.table_name = 'u_device_computers'; //Set the extended importset table
	crImpSet.state = 'loading';
	var ImpSetId = crImpSet.insert();

	
	var xmlString = response.getBody();
	var xmlDoc = new XMLDocument2();
	xmlDoc.parseXML(xmlString);
	var r_size = xmlDoc.getNodeText("//size");
	
	var node = xmlDoc.getNode("//computers");

	var iter = node.getChildNodeIterator();
	
	while (iter.hasNext()) {
		var r_id = xmlDoc.getNodeText("//id");
		var r_name = xmlDoc.getNodeText("//name");
		var r_managed = xmlDoc.getNodeText("//managed");
		var r_username = xmlDoc.getNodeText("//username");
		var r_model = xmlDoc.getNodeText("//model");
		var r_department = xmlDoc.getNodeText("//department");
		var r_building = xmlDoc.getNodeText("//building");
		var r_mac_address = xmlDoc.getNodeText("//mac_address");
		var r_udid = xmlDoc.getNodeText("//udid");
		var r_serial_number = xmlDoc.getNodeText("//serial_number");
		var r_report_date_utc = xmlDoc.getNodeText("//report_date_utc");
		var r_report_date_epoch = xmlDoc.getNodeText("//report_date_epoch");


		var imp = new GlideRecord('u_device_computers');
		imp.initialize();
		imp.u_id = r_id;
		imp.u_name = r_name;
		imp.u_managed = r_managed;
		imp.u_username = r_username;
		imp.u_model = r_model;
		imp.u_department = r_department;
		imp.u_building = r_building;
		imp.u_mac_address = r_mac_address;
		imp.u_udid = r_udid;
		imp.u_serial_number = r_serial_number;
		imp.sys_import_set = ImpSetId;
		imp.insert();
		var n = iter.next();

	}


})(current, previous);