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

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

    anilallewar

      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

        • 1. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
          masilamani

          Anil,

           

          Can you try with below one ?

           

          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[1]') AS t; 

          END

           

          Thanks, Mani

          • 2. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
            anilallewar

            Thanks Mani!!

             

            I already got that working by using array notation in XPath; but the problem is it still doesn't allow me to get all the attributes. I only get the first attribute.

             

            I have some ideas around using the array notation to solve my problem; basically create multiple columns with different positions in the result[]. The only caveat is that my response now can't vary based on the user input.

             

            Anil

            • 3. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
              shawkins

              Array results are supported in Teiid 8.8 with [TEIID-2980] Expose sequences as arrays in XMLTABLE construct - JBoss Issue Tracker

               

              Depending upon your needs you can also your context to be result:

               

              ... '/tns0:getEmpDataByNameResponseElement/tns0:result' PASSING WsView.getEmpDataByName_response.xml_in COLUMNS result string PATH 'text()') AS t;

              • 4. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                anilallewar

                We use Teiid 8.4 and upgrading is not an option as of now. Additionally, I want to convert the 1 dimensional array of result to a 2 dimensional array so that I can group them into rows accordingly.

                 

                To this effect, I am thinking of writing a virtual procedure that uses XSL to transform the 1D XML to 2D and then expose the results via XMLTABLE. I'll probably have more questions around the usage later.

                 

                I did not like the option of selecting the whole XML in the resultset because the client would then know how to parse it; the details are not abstracted out from him.

                • 5. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                  shawkins

                  > I want to convert the 1 dimensional array of result to a 2 dimensional array so that I can group them into rows accordingly.

                   

                  To understand this better do you mean that each result is a row?  If that's the case, then setting the context as the result element is what you want.

                  • 6. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                    anilallewar

                    No, a combination of some results would be a row based on the number of attributes that we passed to the web service to return.

                     

                    So suppose that I asked information for 4 attributes and the criteria matches 3 records then a total of 12 records are returned. However since all the elements have the name <result>, I need to apply transformation to group these results so that we can have 3 rows of 4 columns each.

                    • 7. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                      shawkins

                      So you will need array support as xmltable can only process based upon a fixed number of columns.  The only in between workaround is if there is some general amount of columns that you would want to map to such that you would get null values when actually retrieving fewer.

                      • 8. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                        anilallewar

                        I did the mapping of the column; and that leads us to the next problem.

                         

                        As per the example above for 4 attributes and the criteria matches 3 records then a total of 12 records are returned. I have mapped the 4 columns to the first four elements; but there is no way for me to get the next 4 elements as the next row in the XMLTABLE and so on. Hence my result now has 1 row always.

                        • 9. Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                          shawkins

                          It seems like an odd way to return the result.  If there are just 12 sibling results to represent a 3x4 result, then as you were saying before, you'll have to use xslt or use more complicated xquery:

                           

                          For example with a document like:  <a><b>1</b><b>2</b><b>3</b><b>4</b></a> representing a 2 column result you could do something like:

                           

                          {code}select * from xmltable('let $count := count($d/a/b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/a/b, (($i - 1)*$rows)+1, 2)}</row>' passing doc as d columns col1 string path 'b[1]', col2 string path 'b[2]') as x{code}

                           

                          you would get a result of:

                           

                          1,2

                          3,4

                          • 10. Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                            anilallewar

                            Steve,

                             

                            Thanks for the pointer. I am now running into problems when using namespaces.

                             

                            select * from xmltable('let $count := count($d/a/b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/a/b, (($i - 1)*$rows)+1, 2)}</row>'
                              passing  xmlparse(document '<a><b>1</b><b>2</b><b>3</b><b>4</b></a>') as d columns col1 string path 'b[1]', col2 string path 'b[2]') as x
                            

                            Returns

                            col1col2
                            12
                            34

                             

                            However, if I use namespaces

                             

                            select * from xmltable(XMLNAMESPACES('http://an.com/types/' AS ns0),
                              'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' 
                              PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH 'b[1]') x
                            

                            Returns

                            id
                            <null>
                            <null>

                             

                            Changing the path to '.' gives concatenated results.

                             

                            select * from xmltable(XMLNAMESPACES('http://a.com/types/' AS ns0),
                              'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' 
                              PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH '.') x
                            

                            Returns

                            id

                            12

                            34

                             

                            So now I am not able to convert the result to columns as the subsequence doesn't provide them as separate columns. Any ideas?

                            • 11. Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                              shawkins

                              In your first use of namespaces above you have the XMLNAMESPACES defined with an.com vs the document with a.com.  You also need to use the namespace prefix in the path - 'ns0:b[1]'.

                               

                              A small side note, you can also choose to ignore namespaces by using the * prefix.

                              • 12. Re: Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
                                anilallewar

                                The prefix of namespace in the XPath was the problem. The below query works fine.

                                 

                                select * from xmltable(XMLNAMESPACES('http://a.com/types/' AS ns0), 
                                  'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' 
                                  PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH 'ns0:b[1]', id1 string path 'ns0:b[2]') x
                                

                                Thanks a lot Steve!!