Parsing SOAP XML response - Loop 1 element for variable names and link to N elements for values

Highlighted
Four Stars

Parsing SOAP XML response - Loop 1 element for variable names and link to N elements for values

Hey, 

 

I am having some trouble figuring out how to parse a XML response from a SOAP web service that I consume. The full integration will have a few hundred variables, but here is a truncated example XML with some redacted information:

 

<?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>
	<InvokeMethodResponse xmlns="http://someExample.com/webservices/WebServiceInterface">
      <InvokeMethodResult>
        <DataSet xmlns="">
          <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="Results">
            <xs:element name="Results" msdata:IsDataSet="true" msdata:Locale="fi-FI">
              <xs:complexType>
                <xs:choice minOccurs="0" maxOccurs="unbounded">
                  <xs:element name="Values" msdata:Locale="fi-FI">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="ts" type="xs:dateTime" />
                        <xs:element name="value0" msdata:Caption="Tagname_A:av Avg value (SERVER!Tagname_A:av)" type="xs:float" minOccurs="0" />
                        <xs:element name="status0" msdata:Caption="Tagname_A:av Avg status (SERVER!Tagname_A:av)" type="xs:string" minOccurs="0" />
                        <xs:element name="value1" msdata:Caption="Tagname_B:av Avg value (SERVER!Tagname_B:av)" type="xs:float" minOccurs="0" />
                        <xs:element name="status1" msdata:Caption="Tagname_B:av Avg status (SERVER!Tagname_B:av)" type="xs:string" minOccurs="0" />
                        <xs:element name="value2" msdata:Caption="Tagname_C:me Avg value (SERVER!Tagname_C:me)" type="xs:float" minOccurs="0" />
                        <xs:element name="status2" msdata:Caption="Tagname_C:me Avg status (SERVER!Tagname_C:me)" type="xs:string" minOccurs="0" /> 
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:choice>
              </xs:complexType>
              <xs:unique name="Constraint1" msdata:PrimaryKey="true">
                <xs:selector xpath=".//Values" />
                <xs:field xpath="ts" />
              </xs:unique>
            </xs:element>
          </xs:schema>
          <diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
            <Results>
              <Values diffgr:id="Values1" msdata:rowOrder="0">
                <ts>2019-04-10T15:00:00+03:00</ts>
                <value0>46.1837425</value0>
                <status0>Good</status0>
                <value1>5.02286</value1>
                <status1>Good</status1>
                <value2>26.2978439</value2>
                <status2>Good</status2>
              </Values>
            </Results>
          </diffgr:diffgram>
        </DataSet>
      </InvokeMethodResult>
    </InvokeMethodResponse>
  </soap:Body>
</soap:Envelope>

 

 

I need to parse the variable names (@msdata:Caption) from the xs:element loop in the xs:sequence element and link those to the variable values in .../diffgr:diffgram/Results/Values.

 

The main problem I have is that there can be an arbitrary number of variables in the response, my goal is to get a schema like:

TagName - String | Value - Double | Status - String

 

I have tried creating a metadata file based on the example, but I can only get it to output "@name" and "@msdata:Caption" fields, none of the actual Values or Status. Do I need to create some custom Java to parse this? 

 

/Jani

Tags (2)
Moderator

Re: Parsing SOAP XML response - Loop 1 element for variable names and link to N elements for values

Hello,

Please have a look at this component tXMLMap which is an advanced component fine-tuned for transforming and routing XML data flow (data of the Document type), especially when processing numerous XML data sources, with or without flat data to be joined. 

https://help.talend.com/reader/oF8xI4O2NvHPfUit2M6rZQ/tef5s62XQ1kK0IzoVtkdSA

Let us know if it is OK with you.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download