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,

Tom

Code:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <LoadContactInfoResponse xmlns="http://www.somedomain.dom/">
      <LoadContactInfoResult>
        <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="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
          <xs:element name="DSContactInfo" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
            <xs:complexType>
              <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="CONTACT_INFO_HDR">
                  <xs:complexType>
                    <xs:sequence>
                      <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" />
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:choice>
            </xs:complexType>
          </xs:element>
        </xs:schema>
        <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">
              <cust_contact_id>22611</cust_contact_id>
              <contact_type_id>0</contact_type_id>
              <type>1</type>
              <contact_value>9995551212</contact_value>
              <notes>This is a note.</notes>
              <notification>0</notification>
              <privacy>0</privacy>
              <row_status>A</row_status>
              <last_maintenance_dt>Sep  7 2017  3:41:10:916PM</last_maintenance_dt>
              <last_maintenance_userid>tomr                </last_maintenance_userid>
              <error_code>0</error_code>
            </CONTACT_INFO_HDR>
            <CONTACT_INFO_HDR diffgr:id="CONTACT_INFO_HDR2" msdata:rowOrder="1">
              <cust_contact_id>73632</cust_contact_id>
              <contact_type_id>9</contact_type_id>
              <type>3</type>
              <contact_value>name@domain.dom</contact_value>
              <notes>This is a note, too.</notes>
              <notification>0</notification>
              <privacy>0</privacy>
              <row_status>A</row_status>
              <last_maintenance_dt>Sep  7 2017  3:41:16:763PM</last_maintenance_dt>
              <last_maintenance_userid>tomr                </last_maintenance_userid>
              <error_code>0</error_code>
            </CONTACT_INFO_HDR>
          </DSContactInfo>
        </diffgr:diffgram>
      </LoadContactInfoResult>
    </LoadContactInfoResponse>
  </soap:Body>
</soap:Envelope>


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

Top
#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: 106
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.

Top
#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?

Tom

Top
#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
Hi,

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.

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

Registered: 09/06/00
Posts: 1931
Loc: Objectif Lune, Montreal, Qc
The VBScript code to select the proper node should look something like this:
Code:
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

Top