Topic Options
#55268 - 09/07/17 03:51 PM Search XML for specific entry
Tom Rymes Offline
OL Toddler

Registered: 02/11/08
Posts: 43
Loc: NH, USA
I know that this is a newbie question, but I cannot seem to locate the best way to accomplish this task. I have a SOAP Client action that returns XML similar to what I have included below. The XML includes multiple nodes with the name "CONTACT_INFO_HDR", and I need to locate the one (and only one) with a "contact_type_id" of "9" and return the values of the underlying keys to variables.

Can anyone suggest the easiest way of doing this? A VB script comes to mind, but I'm thinking that there ought to be an easier way, say by using "Set Job Infos and Variables".

Any help appreciated,


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="" xmlns:xsi="" xmlns:xsd="">
    <LoadContactInfoResponse xmlns="http://www.somedomain.dom/">
        <xs:schema id="DSContactInfo" targetNamespace="http://www.somdomain.dom/DSContactInfo.xsd" xmlns:mstns="http://www.somdomain.dom/DSContactInfo.xsd" xmlns="http://www.somdomain.dom/DSContactInfo.xsd" xmlns:xs="" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
          <xs:element name="DSContactInfo" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
              <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="CONTACT_INFO_HDR">
                      <xs:element name="cust_contact_id" type="xs:int" default="0" minOccurs="0" />
                      <xs:element name="contact_type_id" type="xs:short" default="0" minOccurs="0" />
                      <xs:element name="type" type="xs:short" default="0" minOccurs="0" />
                      <xs:element name="contact_value" type="xs:string" default="  " minOccurs="0" />
                      <xs:element name="notes" type="xs:string" default=" " minOccurs="0" />
                      <xs:element name="notification" type="xs:unsignedByte" default="0" minOccurs="0" />
                      <xs:element name="privacy" type="xs:unsignedByte" default="0" minOccurs="0" />
                      <xs:element name="row_status" type="xs:string" default=" " minOccurs="0" />
                      <xs:element name="last_maintenance_dt" type="xs:string" default=" " minOccurs="0" />
                      <xs:element name="last_maintenance_userid" type="xs:string" default=" " minOccurs="0" />
                      <xs:element name="error_code" type="xs:int" default="0" minOccurs="0" />
        <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
          <DSContactInfo xmlns="http://www.somdomain.dom/DSContactInfo.xsd">
            <CONTACT_INFO_HDR diffgr:id="CONTACT_INFO_HDR1" msdata:rowOrder="0">
              <notes>This is a note.</notes>
              <last_maintenance_dt>Sep  7 2017  3:41:10:916PM</last_maintenance_dt>
              <last_maintenance_userid>tomr                </last_maintenance_userid>
            <CONTACT_INFO_HDR diffgr:id="CONTACT_INFO_HDR2" msdata:rowOrder="1">
              <notes>This is a note, too.</notes>
              <last_maintenance_dt>Sep  7 2017  3:41:16:763PM</last_maintenance_dt>
              <last_maintenance_userid>tomr                </last_maintenance_userid>

Edited by Tom Rymes (09/07/17 04:21 PM)

#55271 - 09/08/17 08:59 AM Re: Search XML for specific entry [Re: Tom Rymes]
MartinS Offline
OL Guru

Registered: 08/06/12
Posts: 107
Loc: Munich
you could try to split the XML data, either by using a XML Splitter (but not sure if that works on that sort of XML) or a generic splitter (maybe with line printer emulation) and evaluating the contact_type_id for each split part, if it has value 9 you could extract the following values in another loop.

#55272 - 09/08/17 11:22 AM Re: Search XML for specific entry [Re: Tom Rymes]
Tom Rymes Offline
OL Toddler

Registered: 02/11/08
Posts: 43
Loc: NH, USA
Answering my own question here, but the best I have come up with is to remove the first 31 lines and the last 5 lines of the XML to get down to just the portion of the SOAP response that contains the data I want.

From there, I use the XML Splitter to isolate each item and read variables. I presume that there's a better way to accomplish this, perhaps by specifying a namespace or other detail in the XML splitter (thus avoiding the need to add/remove lines), or perhaps something else.

Can anyone provide some input?


#55275 - 09/11/17 05:32 AM Re: Search XML for specific entry [Re: Tom Rymes]
Hunt3r Offline
OL Newbie

Registered: 02/13/17
Posts: 13

Since you cannot use XPATH in the plugins, I would open the job file with script, and use XPATH (eg, "(//CONTACT_INFO_HDR[contact_type_id = '9'])[1]") to select the element.

Using loops can be slow, and I think around 6 lines of code is not too much, and it is much easier.

#55286 - 09/13/17 07:44 AM Re: Search XML for specific entry [Re: Tom Rymes]
Philippe F. Offline
OL Expert

Registered: 09/06/00
Posts: 1945
Loc: Objectif Lune, Montreal, Qc
The VBScript code to select the proper node should look something like this:
set myXML = CreateObject("Msxml2.DOMDocument.6.0")
myXML.async = false
myXML.Load Watch.GetJobFileName
set targetElement = myXML.SelectSingleNode("//*[local-name()='CONTACT_INFO_HDR']/*[local-name()='contact_type_id' and text()='9']/..")

Watch.Log targetElement.SelectSingleNode("./*[local-name()='cust_contact_id']").text,2
Watch.Log targetElement.SelectSingleNode("./*[local-name()='contact_value']").text,2
'' etc.

The last few lines show how to extract values from nodes under the CONTACT_INFO_HDR node that was selected. This entire code is made slightly more complex than a usual XPATH because we are dealing with XML namespaces, but other than that it's pretty straightforward.
Technical Product Manager
I don't want to achieve immortality through my work; I want to achieve immortality through not dying - Woody Allen