In the first part of this post I used the WSDL into Relational Model Importer to generate a set or relational models for a SOAP service, and walked through the artifacts created by the importer.  In this second part I'm going to demonstrate how to execute the service with those models.

The first thing we're going to do is to put the metadata required to create a Teiid data source into the physical model for the service, and then create a data source in Teiiid.  I'm assuming you've already established an admin connection to Teiid.

To add the data source metadata to the model, right-click on the CountryInfoService model in the Model Explorer and select Modeling->Set Connection Profile.  Select the Country Info Service connection profile from the presented dialog and save the model changes.  To create the data source in Teiid:

• Right-click on the model again and select Modeling->Create Teiid Data Source
• Click Finish. 

CountryInfoService will appear within the Data Sources folder of your default Teiid server.

Next, I want to create another virtual model to contain the transformations that will create the requests, the execute the operations, and the parse the responses.

• Right click on the CountryInfo Teiid Model Project and select New->Teiid Metadata Model
• Enter a model name, I'm calling it ops, leave the Model Class as Relational, and select a Model Type of View Model
• Click Finish 
• Right-click the new ops model and select New Child->Procedure
• Rename NewProcedure to GetCapitalCity and NewProcedureResult to CapitalCity
• Right-click on the GetCapitalCity procedure and select New Child->Procedure Parameter to create an input parameter called countryISOCode of type String.

Next you need to add the procedures to the transformation diagram.  There are many paths to to this, but I prefer to just drad the needed transformation resources in.  Double click the GetCapitalCity procedure to open up the transformation diagram, and drag the physical invoke( ) procedure from the CountryInfoService model into the sources portion of the transformation diagram, and the createCapitalCity and extractCapitalCity procedures from the CapitalCity schema.

Click the picture for a better resolution.

You can paste in the following SQL as the content of the virtual procedure.  It's pretty straight forward.  The SQL executes the create_CapitalCity procedure passing along the ISOCode paramter.  The xml created in that procedure is passed as the request paramter to the invoke call.  The result of the invoke call is passed to the extract_CapitalCity procedure to parse out the CapitalCity name, and that value forms the result.

CREATE VIRTUAL PROCEDURE
BEGIN
     SELECT t.* FROM 
          TABLE(EXEC CountryInfoServiceXML.CapitalCity.create_CapitalCity(OPS.GETCAPITALCITY.countryISOCode)) 
     AS request, 
     TABLE(EXEC CountryInfoService.invoke(null, null, REQUEST.xml_out, null)) 
     AS response, 
     TABLE(EXEC CountryInfoServiceXML.CapitalCity.extract_CapitalCityResponse(RESPONSE.result)) 
     AS t;
END

Note here that we're only passing along the request to the invoke calland leaving the other params null.  We're relying on the data source to provide the binding, SOAP Action and endpoint.

If you prefer tables, you can wrap this procedure in a virtual table with columns for CapitalCity and ISOCode and an Access Pattern on the ISOCode.  Here's what the transformation would look like for that table.

SELECT
        CapitalCityResult AS CapitalCity, countryISOCode AS ISOCode
    FROM
        ops.GetCapitalCity AS occ
    WHERE
        countryISOCode = countryISOCode

Note also that we've now abstracted away any notion that there's a SOAP call or XML being handled in this procedure or table.

The new SQL/XML functions in Teiid are capable of much more complex processing than the baby steps I've shown here.  Hopefully these posts will provide you with enough info to get started modeling SOAP Services in your VDBs.