JBoss VDB Issue accessing WebService as a source

[sonyjop]

I created a VDB using a WebService as a source.

The VDB has got a Virtual Procedure which inturns accesses a WebService and then returns a resultset.

On shooting a query (in SQuirrelClient) The VDB throws exception.

Details as below

JBoss EAP - version 6.1

VDB - version 8.7

Exception : TEIID30020 Processing exception for request pBL1ZoGB7PX2.5 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException 'The prefix "xsi" for attribute "xsi:nil" associated with an element type "Point" is not bound.' org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source). Enable more detailed logging to see the entire stacktrace.

==> indicates the prefix "xsi" is not bound. Infact the SOAP response has portion as below: where xsi should be equal to xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

....

               <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

......

[sonyjop]

I thought of adding a few more detailed exception:

WARN  [org.teiid.PROCESSOR] (Worker28_QueryProcessorQueue84) zXyfuO/q+Gsb TEIID30020 Processing exception for request zXyfuO/q+Gsb.0 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException 'The prefix "xsi" for attribute "xsi:nil" associated with an element type "Point" is not bound.' org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source). Enable more detailed logging to see the entire stacktrace.

And I have mentioned the Virtual Procedure to process the WebService Response as below:

SELECT t.X, t.Y FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://tempuri.org/', 'http://www.w3.org/2001/XMLSchema' AS xsd, 'http://www.w3.org/2001/XMLSchema-instance' AS xsi) ,

            '/getPointsResponse/getPointsResult/Point'

  PASSING xml_in  COLUMNS

  X string PATH 'X',

  Y string PATH 'Y') AS t;

[rareddy]

How does your document returned from the web service look like exactly? See similar error here Specifying xsi:nil="true" on a webservice xml element - Mirth Community

I am guessing your document returned from the web service may be missing the XMLSchema-instance namespace?

[sonyjop]

Many thanks for responding. Please have a look at the XML coming in response.

I tried the VDB file in teiid 8.12.x and it works like a charm. But our production environment is got teiid 8.7 and it got to work there.

I am not sure if I update the SAX Parser related packages! could it improve.

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

   <soap:Body>

      <getPointsResponse xmlns="<soneNamespace>">

         <getPointsResult>

            <Point>

               <X>467</X>

               <Y>23</Y>

            </Point>

            <Point>

               <X>0</X>

               <Y>111.81</Y>

            </Point>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

            <Point xsi:nil="true"/>

  </getPointsResult>

     </getPointsResponse>

   </soap:Body>

</soap:Envelope>

[sonyjop]

To add.. I ahve create abelow model and it worked like charm. When connecting with your suggestion, I understand that the namespace http://www.w3.org/2001/XMLSchema-instance  is not available in the actual webservice response but only the path starting from <getPointsResponse xmlns="http://tempuri.org/">. So the document which arrives for XMLTABLE could not be parsed. Now I wonder whether CAN I INDUCE A NAMESPACE INTO THE RESPONSE?? or do you see any other work around?

CREATE VIEW StockPrices (

            symbol string,

            price string

            ) AS

            SELECT t.X, t.Y

  FROM

  XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns, 'http://www.w3.org/2001/XMLSchema' AS xsd, 'http://www.w3.org/2001/XMLSchema-instance' AS xsi, 'http://www.w3.org/2003/05/soap-envelope' AS soap) ,

            '/soap:Envelope/soap:Body/tns:getPointsResponse/tns:getPointsResult/tns:Point'

  PASSING convert('

  <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <soap:Body>

    <getPointsResponse xmlns="http://tempuri.org/">

  <getPointsResult>

  <Point>

    <X>1</X>

    <Y>1</Y>

  </Point>

  <Point>

    <X>0</X>

    <Y>1365.81</Y>

  </Point>

  <Point>

    <X>201.52</X>

    <Y>1368.4</Y>

  </Point>

  <Point xsi:nil="true"/>

  </getPointsResult>

   </getPointsResponse>

   </soap:Body>

   </soap:Envelope>

',xml)

  COLUMNS

  X string PATH '/tns:X',

  Y string PATH '/tns:Y') AS t;

[rareddy]

Yes, what you have is fine. I am guessing 8.7 did not have the implicit namespace of xsi not available, and where as 8.12 does.

[sonyjop]

Thanks Ramesh,

Apologies for the delayed response. I have gotten it working without a problem in TEIID version 8.12.