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

[SD40]

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....

[RaghavSh]

Refer https://docs.servicenow.com/en-US/bundle/sandiego-application-development/page/script/server-scripti...

[Ankur Bawiskar]

Hi,

you can convert that XML to JSON and then create those many records into import set table

Once record is inserted into staging table; the associated transform maps, field maps will run on their own

Regards
Ankur

[SD40]

Hi Ankur,

I have tried that but due to regex(:), I cannot fetch the data.. 

[ChrisBurks · Accepted solution]

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.