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.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download