How to parse the XML data received from SOAP response and import into staging table

Go to solution
SD40
Tera Contributor

‎06-15-2022 12:03 AM

I am getting SOAP response like this:

<soap:Envelope xmlns:soap="http://org/soap/envelope/" xmlns:xsd="http://2001/XMLSchema" xmlns:xsi="http://g/2001/XMLSchema-instance">
<soap:Body xmlns:ns1="http://com/stdws/xsd/ISXCentralDevices-v2">
 
<ns1:getAllDevicesResponse>
<ns1:ArrayOfISXCDevice>
 
<ns2:ISXCDevice xmlns:ns2="http://com/stdws//2009/10">
 
<ns2:ISXCNamedElement>
<ns2:ISXCElement>
<ns2:ISXCElementType>DEVICE</ns2:ISXCElementType>
<ns2:id>Bbeaacc_nbSNMPEncFF523CF8</ns2:id>
</ns2:ISXCElement>
<ns2:name>UPS31.8</ns2:name>
</ns2:ISXCNamedElement>
 
<ns2:ISXCCommState>ONLINE</ns2:ISXCCommState>
<ns2:ISXCDeviceType>PS</ns2:ISXCDeviceType>
<ns2:hostName>10.1.131.8</ns2:hostName>
<ns2:ipAddress>10.7.131.8</ns2:ipAddress>
<ns2:location>S1 MA</ns2:location>
<ns2:modelName>Smart-UPS 1500</ns2:modelName>
<ns2:modelNumber/>
<ns2:parentID>Bbeaacc</ns2:parentID>
<ns2:serialNumber>AS1326230695</ns2:serialNumber>
<ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState>
<ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental>
 
</ns2:ISXCDevice>
 
 
<ns2:ISXCDevice xmlns:ns2="http://com/stdws/2009/10">
<ns2:ISXCNamedElement>
<ns2:ISXCElement>
<ns2:ISXCElementType>DEVICE</ns2:ISXCElementType>
<ns2:id>Bbeaacc_nbSNMPEncA5AD44E4</ns2:id>
</ns2:ISXCElement>
<ns2:name>GASrear (21.65)</ns2:name>
</ns2:ISXCNamedElement>
<ns2:ISXCCommState>ONLINE</ns2:ISXCCommState>
<ns2:ISXCDeviceType>UPS</ns2:ISXCDeviceType>
<ns2:hostName>10.9.21.6</ns2:hostName>
<ns2:ipAddress>10.2.2.65</ns2:ipAddress>
<ns2:location>91G - Cutler Bay, FL</ns2:location>
<ns2:modelName>Smart-UPS 15</ns2:modelName>
<ns2:modelNumber/>
<ns2:parentID>Bbeaacc</ns2:parentID>
<ns2:serialNumber>AS15851</ns2:serialNumber>
<ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState>
<ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental>
</ns2:ISXCDevice>
 
</ns1:ArrayOfISXCDevice>
</ns1:getAllDevicesResponse>
</soap:Body>
</soap:Envelope>
 
How can I parse this response and get those field values?
ex- ElementType=DEVICE,name=UPS31....
Labels:
0 Helpfuls
  • 3,604 Views
1 ACCEPTED SOLUTION

Go to solution
ChrisBurks
Mega Sage

‎06-15-2022 05:59 AM

You could use the reference that Raghav posted or use the newer version from here:

https://developer.servicenow.com/dev.do#!/reference/api/quebec/server/no-namespace/c_XMLDocument2ScopedAPI?navFilter=xml

 

Using the above you would make your xml a string.

var xmlStr = '<soap:Envelope xmlns:soap="http://org/soap/envelope/" xmlns:xsd="http://2001/XMLSchema" xmlns:xsi="http://g/2001/XMLSchema-instance"><soap:Body xmlns:ns1="http://com/stdws/xsd/ISXCentralDevices-v2"> <ns1:getAllDevicesResponse><ns1:ArrayOfISXCDevice> <ns2:ISXCDevice xmlns:ns2="http://com/stdws//2009/10"> <ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncFF523CF8</ns2:id></ns2:ISXCElement><ns2:name>UPS31.8</ns2:name></ns2:ISXCNamedElement> <ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>PS</ns2:ISXCDeviceType><ns2:hostName>10.1.131.8</ns2:hostName><ns2:ipAddress>10.7.131.8</ns2:ipAddress><ns2:location>S1 MA</ns2:location><ns2:modelName>Smart-UPS 1500</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS1326230695</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental> </ns2:ISXCDevice>  <ns2:ISXCDevice xmlns:ns2="http://com/stdws/2009/10"><ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncA5AD44E4</ns2:id></ns2:ISXCElement><ns2:name>GASrear (21.65)</ns2:name></ns2:ISXCNamedElement><ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>UPS</ns2:ISXCDeviceType><ns2:hostName>10.9.21.6</ns2:hostName><ns2:ipAddress>10.2.2.65</ns2:ipAddress><ns2:location>91G - Cutler Bay, FL</ns2:location><ns2:modelName>Smart-UPS 15</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS15851</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental></ns2:ISXCDevice> </ns1:ArrayOfISXCDevice></ns1:getAllDevicesResponse></soap:Body></soap:Envelope>';

Then declare a variable with a new instance of the XMLDocument2 object and use the parseXML method to parse the string by passing the variable holding the xml as an argument.

var xmlDoc = new XMLDocument2();
xmlDoc.parseXML(xmlStr);

From there the XML actually helps you out as it states which node is an array of the devices:

<ns1:ArrayOfISXCDevice>

With that information use the getNode to target that element and its containing elements by using XPath and the name of the element

var nodeArray = xmlDoc.getNode("//ns1:ArrayOfISXCDevice");

From here use the getChildNode

var iter= nodeArray.getChildNodeIterator();

Now use the iterator methods  hasNext and next with a loop to iterate through each device element to get the values as well as write your script to push into the staging table fields

while (iter.hasNext()){
    var id, name, commState, deviceType, hostName, ipAdd, location, modelName, modelNumber, parentId, serialNumber, deviceState, supplemental;
     var n = iter.next();
     var nodeName = n.getNodeName();
     if(nodeName == "ns2:ISXCDevice"){
        xmlDoc.parseXML(n)
        id = xmlDoc.getNodeText("//ns2:id");
        name = xmlDoc.getNodeText("//ns2:name");
        commState = xmlDoc.getNodeText("//ns2:ISXCCommState");
        deviceType = xmlDoc.getNodeText("//ns2:ISXCDeviceType");
        hostName = xmlDoc.getNodeText("//ns2:hostName");
        ipAdd = xmlDoc.getNodeText("//ns2:ipAddress");
        location = xmlDoc.getNodeText("//ns2:location");
        modelName = xmlDoc.getNodeText("//ns2:modelName");
        modelNumber = xmlDoc.getNodeText("//ns2:modelNumber");
        parentId = xmlDoc.getNodeText("//ns2:parentID");
        serialNumber = xmlDoc.getNodeText("//ns2:serialNumber");
        deviceState = xmlDoc.getNodeText("//ns2:ISXCDeviceState");
        supplemental = xmlDoc.getNodeText("//ns2:supplemental");
       
       // write GlideRecord script pointing to your staging table to push values into each staging field
     }
    
}

Or instead of making a variable for each value just straight write the GlideRecord script to the staging table and push the values into the correlating fields.

All together the script should be similar to this:

var xmlStr = '<soap:Envelope xmlns:soap="http://org/soap/envelope/" xmlns:xsd="http://2001/XMLSchema" xmlns:xsi="http://g/2001/XMLSchema-instance"><soap:Body xmlns:ns1="http://com/stdws/xsd/ISXCentralDevices-v2"> <ns1:getAllDevicesResponse><ns1:ArrayOfISXCDevice> <ns2:ISXCDevice xmlns:ns2="http://com/stdws//2009/10"> <ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncFF523CF8</ns2:id></ns2:ISXCElement><ns2:name>UPS31.8</ns2:name></ns2:ISXCNamedElement> <ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>PS</ns2:ISXCDeviceType><ns2:hostName>10.1.131.8</ns2:hostName><ns2:ipAddress>10.7.131.8</ns2:ipAddress><ns2:location>S1 MA</ns2:location><ns2:modelName>Smart-UPS 1500</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS1326230695</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental> </ns2:ISXCDevice>  <ns2:ISXCDevice xmlns:ns2="http://com/stdws/2009/10"><ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncA5AD44E4</ns2:id></ns2:ISXCElement><ns2:name>GASrear (21.65)</ns2:name></ns2:ISXCNamedElement><ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>UPS</ns2:ISXCDeviceType><ns2:hostName>10.9.21.6</ns2:hostName><ns2:ipAddress>10.2.2.65</ns2:ipAddress><ns2:location>91G - Cutler Bay, FL</ns2:location><ns2:modelName>Smart-UPS 15</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS15851</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental></ns2:ISXCDevice> </ns1:ArrayOfISXCDevice></ns1:getAllDevicesResponse></soap:Body></soap:Envelope>';

var xmlDoc = new XMLDocument2();
xmlDoc.parseXML(xmlStr);
var nodeArray = xmlDoc.getNode("//ns1:ArrayOfISXCDevice");
var iter = nodeArray.getChildNodeIterator();
while (iter.hasNext()){
    var id, name, commState, deviceType, hostName, ipAdd, location, modelName, modelNumber, parentId, serialNumber, deviceState, supplemental;
     var n = iter.next();
     var nodeName = n.getNodeName();
     if(nodeName == "ns2:ISXCDevice"){
        xmlDoc.parseXML(n)
        id = xmlDoc.getNodeText("//ns2:id");
        name = xmlDoc.getNodeText("//ns2:name");
        commState = xmlDoc.getNodeText("//ns2:ISXCCommState");
        deviceType = xmlDoc.getNodeText("//ns2:ISXCDeviceType");
        hostName = xmlDoc.getNodeText("//ns2:hostName");
        ipAdd = xmlDoc.getNodeText("//ns2:ipAddress");
        location = xmlDoc.getNodeText("//ns2:location");
        modelName = xmlDoc.getNodeText("//ns2:modelName");
        modelNumber = xmlDoc.getNodeText("//ns2:modelNumber");
        parentId = xmlDoc.getNodeText("//ns2:parentID");
        serialNumber = xmlDoc.getNodeText("//ns2:serialNumber");
        deviceState = xmlDoc.getNodeText("//ns2:ISXCDeviceState");
        supplemental = xmlDoc.getNodeText("//ns2:supplemental");
       
       // write script to push values into each staging
     }
    
}

 

Hopefully this helps.

View solution in original post

8 REPLIES 8

Go to solution
ChrisBurks
Mega Sage
In response to ChrisBurks

‎06-15-2022 06:45 AM

Another possible solution depending upon how you defined your staging table fields:

var xmlStr = '<soap:Envelope xmlns:soap="http://org/soap/envelope/" xmlns:xsd="http://2001/XMLSchema" xmlns:xsi="http://g/2001/XMLSchema-instance"><soap:Body xmlns:ns1="http://com/stdws/xsd/ISXCentralDevices-v2"> <ns1:getAllDevicesResponse><ns1:ArrayOfISXCDevice> <ns2:ISXCDevice xmlns:ns2="http://com/stdws//2009/10"> <ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncFF523CF8</ns2:id></ns2:ISXCElement><ns2:name>UPS31.8</ns2:name></ns2:ISXCNamedElement> <ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>PS</ns2:ISXCDeviceType><ns2:hostName>10.1.131.8</ns2:hostName><ns2:ipAddress>10.7.131.8</ns2:ipAddress><ns2:location>S1 MA</ns2:location><ns2:modelName>Smart-UPS 1500</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS1326230695</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental> </ns2:ISXCDevice>  <ns2:ISXCDevice xmlns:ns2="http://com/stdws/2009/10"><ns2:ISXCNamedElement><ns2:ISXCElement><ns2:ISXCElementType>DEVICE</ns2:ISXCElementType><ns2:id>Bbeaacc_nbSNMPEncA5AD44E4</ns2:id></ns2:ISXCElement><ns2:name>GASrear (21.65)</ns2:name></ns2:ISXCNamedElement><ns2:ISXCCommState>ONLINE</ns2:ISXCCommState><ns2:ISXCDeviceType>UPS</ns2:ISXCDeviceType><ns2:hostName>10.9.21.6</ns2:hostName><ns2:ipAddress>10.2.2.65</ns2:ipAddress><ns2:location>91G - Cutler Bay, FL</ns2:location><ns2:modelName>Smart-UPS 15</ns2:modelName><ns2:modelNumber/><ns2:parentID>Bbeaacc</ns2:parentID><ns2:serialNumber>AS15851</ns2:serialNumber><ns2:ISXCDeviceState>NONE</ns2:ISXCDeviceState><ns2:supplemental>InputPhaseCount="1";OutputPhaseCount="1";</ns2:supplemental></ns2:ISXCDevice> </ns1:ArrayOfISXCDevice></ns1:getAllDevicesResponse></soap:Body></soap:Envelope>';

var xmlDoc = new XMLDocument2();
xmlDoc.parseXML(xmlStr);
var nodeArray = xmlDoc.getNode("//ns1:ArrayOfISXCDevice");
var iter = nodeArray.getChildNodeIterator();
while (iter.hasNext()){
     var n = iter.next();
     if(n.getNodeName() != "#text"){
        var deviceIter = n.getChildNodeIterator();
        var staging = new GlideRecord('<staging_table_name_here>');
        staging.initialize();

        while (deviceIter.hasNext()){
             var nx = deviceIter.next();
             if(nx.getNodeName() != "#text"){
                var snFieldName = "u_" + nx.getNodeName().replace('ns2:', '').toLowerCase(); //example u_isxccommState
                staging.setValue(snFieldName, nx.getNodeValue());
             }
        }
        staging.insert();
     }
    
}
0 Helpfuls

Go to solution
SD40
Tera Contributor
In response to ChrisBurks

‎06-15-2022 07:19 AM

Hi Chris,

Thank you, it's now working as expected.

0 Helpfuls

Go to solution
SD40
Tera Contributor
In response to ChrisBurks

‎06-15-2022 07:20 AM

Hi Chris,

Thank you, it's now working as expected.

0 Helpfuls

Go to solution
SD40
Tera Contributor
In response to ChrisBurks

‎06-15-2022 07:20 AM

Hi Chris,

Thank you, it's now working as expected.

0 Helpfuls