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

[Anonymous]

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

[Anonymous]

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