Since this problem still exists, re-post it.
I have a WS operation named "getSubjectContainer" whose real implementation is to get all rows from a table.
In Teiid virtual model, I write a "SELECT" procedure as following:
(EXEC jalenPocDrWs.invoke(binding => 'SOAP11', action => null, request => '<ns:getSubjectContainer xmlns:ns="http://ws.dr.poc.jalen"/>', endpoint => null)) AS fr, XMLTABLE(XMLNAMESPACES('http://ws.dr.poc.jalen' AS ns, 'http://ws.dr.poc.jalen/xsd' AS ax21), '$d/ns:getSubjectContainerResponse/ns:return/ax21:subjects' PASSING result AS d COLUMNS sub_id integer PATH 'ax21:subId', sub_keyword string PATH 'ax21:subKeyword', sub_type string PATH 'ax21:subType') AS t
The question is this WS operation is to retrieve all rows in table. So even client call "select * from <table> where id = 7", in the WS side, it will retrieve all data in DB and then return one row whose id is 7. It will be a performance issue if there are many rows in the table.
Is there any trick for this case? WS implementation should be care of this logic or it is the procedure's responsibility to judge whether client is retrieving some rows or all rows?
If I understand correctly, you're saying that the getSubjectContainer operation does not take any parameters that will allow you to narrow the result?
If that's the case, then there's no alternative for you in how you call the service, but you might be able to improve the performance of your call if you parse the response with XQuery rather than using XMLTABLE. As you say, XMLTABLE will create a table of the full result. A properly written XQuery could filter the XML response down to just the elements where id = 7. The result of that could then be passed to XMLTABLE.
You could create another procedure to create your request document. Then you can use the procedural relational syntax. For example:
getSubjectContainer as REQUEST, TABLE (EXEC jalenPocDrWs.invoke(binding => 'SOAP11', request =>REQUEST.result)) AS fr, XMLTABLE(XMLNAMESPACES('http://ws.dr.poc.jalen' AS ns, 'http://ws.dr.poc.jalen/xsd' AS ax21), '$d/ns:getSubjectContainerResponse/ns:return/ax21:subjects' PASSING result AS d COLUMNS sub_id integer PATH 'ax21:subId', sub_keyword string PATH 'ax21:subKeyword', sub_type string PATH 'ax21:subType') AS t
getSubjectContainer would be expected to have an input parameter that is mapped to the id column and a single column resultset with the document. However if you take this approach then you would be required to use an equality or other predicate on id to supply a value to the procedure.
Still a question regarding the WS. How to use Teiid to construct the SOAP HEADER message.
CREATE VIRTUAL PROCEDURE
SELECT XMLELEMENT(NAME insertSubject, XMLNAMESPACES(DEFAULT 'http://ws.dr.poc.jalen'), XMLELEMENT(NAME subject, XMLELEMENT(NAME subId, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subId), XMLELEMENT(NAME subKeyword, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subKeyword), XMLELEMENT(NAME subType, JALENPOCDRWSXML.INSERTSUBJECT.CREATE_INSERTSUBJECT.subType))) AS xml_out;
The above procedure can only construct the message in SOAP BODY instead of SOAP HEADER. As you know, some WS need to cache information in SOAP Header. How to construct it in Teiid?
If you know what XML fragments you need for the SOAP HEADER could you not create them manually using the same "XMLElement" constructs? Like
XMLElement(NAME "soap-header" ...)
Note that when you build the soap-header, you need to switch the WS execution to PAYLOAD mode from MESSAGE. You can find this in the WS translator's properties.