Parsing through an XML String

[Patrick Quinlan]

Ok guys and gals....

I am currently building a service request that needs to do an on demand JDBCProbe to retrieve data from a database. I have the onChange Client script and Script include working which successfully inserts a record into the ECC Queue for processing and my Script Include utilizes the sys_id of the output message to retrieve the returned data. The problem I'm having at the moment is that the returned payload may have multiple entries of data that I need to display in the request and I seem to be unable to parse through the payload to retrieve them. 

The payload that is being returned is:

<?xml version="1.0" encoding="UTF-8"?>
<results probe_time="0">
<result query="select work_order_id, description from work_order w where client_id = 1 and status = 'Open' and (expiration_date is null or expiration_date &gt; sysdate) and work_order_id LIKE 'W-000002%'">
	<row id="1">
		<DESCRIPTION class="java.lang.String" length="200" type="-9">CIP-12091-ERP</DESCRIPTION>
		<WORK_ORDER_ID class="java.lang.String" length="30" type="-9">W-00000211</WORK_ORDER_ID></row>
	<row id="2">
		<DESCRIPTION class="java.lang.String" length="200" type="-9">ODC-IP072-ODC</DESCRIPTION>
		<WORK_ORDER_ID class="java.lang.String" length="30" type="-9">W-00000231</WORK_ORDER_ID></row>
	<row id="3">
		<DESCRIPTION class="java.lang.String" length="200" type="-9">ODC-IP073-ODC</DESCRIPTION>
		<WORK_ORDER_ID class="java.lang.String" length="30" type="-9">W-00000261</WORK_ORDER_ID></row>
	<row id="4">
		<DESCRIPTION class="java.lang.String" length="200" type="-9">ODC-V1072-V1</DESCRIPTION>
		<WORK_ORDER_ID class="java.lang.String" length="30" type="-9">W-00000266</WORK_ORDER_ID></row>
	<row id="5">
		<DESCRIPTION class="java.lang.String" length="200" type="-9">R&amp;D-A0001-TEST A C MAINTENANCE</DESCRIPTION>
		<WORK_ORDER_ID class="java.lang.String" length="30" type="-9">W-00000270</WORK_ORDER_ID></row>
</result>
<parameters>
	<parameter name="agent" value="mid.server.nowmiddw001_dv"/>
	<parameter name="sql_statement" value="select work_order_id, description from work_order w where client_id = 1 and status = 'Open' and (expiration_date is null or expiration_date &gt; sysdate) and work_order_id LIKE 'W-000002%'"/>
	<parameter name="jdbc_user_name" value="ServiceNow_Read"/>
	<parameter name="source" value=""/><parameter name="connection_string" value="jdbc:oracle:thin:@oracrtdev01:1521:WFMSAPDV"/>
	<parameter name="skip_sensor" value="true"/>
	<parameter name="sys_id" value="7cd6f04b1be44850f774759d1e4bcb7c"/>
	<parameter name="from_host" value=""/>
	<parameter name="sys_created_on" value="2019-10-21 19:11:09"/>
	<parameter name="sys_domain" value="global"/>
	<parameter name="jdbc_password" value="em2ynBdd68V8vKC7"/>
	<parameter name="jdbc_driver" value="oracle.jdbc.OracleDriver"/>
	<parameter name="state" value="ready"/>
	<parameter name="response_to" value=""/>
	<parameter name="query" value="Specific SQL"/>
	<parameter name="from_sys_id" value=""/>
	<parameter name="priority" value="2"/>
	<parameter name="agent_correlator" value=""/>
	<parameter name="processed" value=""/>
	<parameter name="error_string" value=""/>
	<parameter name="sequence" value="16defbaf3bb0000001"/>
	<parameter name="name" value=""/>
	<parameter name="topic" value="JDBCProbe"/>
	<parameter name="queue" value="output"/>
	<parameter name="ecc_queue" value="7cd6f04b1be44850f774759d1e4bcb7c"/>
</parameters>
</results>

I need a way to parse through this XML payload and return each Description and Work_Order_Id value

As long as there is only 1 Description and Work Order ID in the payload, gs.getXMLText(gr.payload, "//DESCRIPTION"); will work. But it only returns the first one that it finds and then stops. 

How can I loop through this XML to return all entries?

[DScroggins · Accepted solution]

Hello,

You can use XMLHelper to convert to an object then loop through the results like so:

var helper =new global.XMLHelper(<ECC Payload>); 
var obj = helper.toObject();
var res =  obj['result']['row'];
var len = obj['result']['row'].length;

for(var k=0; k<len ; k++){
  
  var desc = res[k]['DESCRIPTION']['#text'];
//Do Something with desc
  
  var wrkOrdID = res[k]['WORK_ORDER_ID']['#text'];
//Do something with wrkOrdID
  
}

Make sure you actually pass in the payload returned from the ECC Queue. Also in the above example its simply setting the values to two variables 'desc' & 'wrkOrdID'. You can then do something with those variables as needed.

Hope this helps.

--David