12 Replies Latest reply on Jun 30, 2014 6:10 PM by Anil Allewar

    Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call

    Anil Allewar Newbie

      I have a SOAP WS that returns document in the format as below where the <result> element is unbounded and can occur "n" number of times.

       

      <ns0:getEmpDataByNameResponseElement xmlns:ns0="............../types/">
        <ns0:result xmlns:ns0="............../types/">xxxxxxxxxx</ns0:result>
        <ns0:result xmlns:ns0="............../types/">xxxxxxxxxx</ns0:result>
      </ns0:getEmpDataByNameResponseElement>
      
      

      I am using an XMLTABLE to parse the response but it fails to parse the response if it has more than 1 element of the name "result". Please see the procedure definition below.

       

      CREATE VIRTUAL PROCEDURE
      BEGIN
        SELECT t.* FROM XMLTABLE(XMLNAMESPACES('.................../types/' AS tns0), '/tns0:getEmpDataByNameResponseElement' PASSING WsView.getEmpDataByName_response.xml_in COLUMNS result string PATH '/tns0:result') AS t;
      END
      
      

       

      The exception that I get is Error: TEIID30171 Unexpected multi-valued result was returned for XMLTable column "result".  Path expressions for non-XML type columns should return at most a single result. This works fine if the returning SOAP response has only 1 result element.

       

      I have been able to get around this by returning the whole XML by defining the result as an XMLLITERAL using the procedure definition below.

       

      CREATE VIRTUAL PROCEDURE
      BEGIN
        SELECT t.* FROM XMLTABLE(XMLNAMESPACES('.................../types/' AS tns0), '/tns0:getEmpDataByNameResponseElement' PASSING WsView.getEmpDataByName_response.xml_in COLUMNS result XML PATH '.') AS t;
      END
      
      

       

      Is there a way to get around so that I can return the array of "result" strings or at best be able to return a subset of the XML? As of now, if I change the XML path, I am always getting null in the results.

       

      Thanks,

      Anil