Skip navigation

Teiid Designer 7.2 Final has been released.  The 7.2 release is compatible with the 7.2 Final release of Teiid and contains a lot of changes to simplify the creation of artifacts for Teiid.  Download the release here and view the release notes here.  Give it a spin.  If you've got questions or problems, post them on the Teiid Designer Forum.  Happy Modeling!

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.


     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;


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.


        CapitalCityResult AS CapitalCity, countryISOCode AS ISOCode
        ops.GetCapitalCity AS occ
        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.

Teiid 7.0 introduced SQL/XML functions for processing XML covering nearly all of SQL/XML 2003, as well as XMLTABLE and XMLQUERY from SQL/XML 2006.  You can find the documentation of the functions in the Teiid Reference Guide, and a wiki entry showing usage of some of the capabilites in the Teiid Community Wiki.


In the 7.1 release of Teiid Designer, the WSDL into Relational Source Model Importer was introduced to make it easier to model SOAP services within a Teiid VDB.  This post will cover the basics of using that importer and specifically cover the benefits it provides you and the limitations of its capabilites.  I'll start by assuming that you have a running Teiid 7.1 server and a 7.1 Teiid Designer instance.


For an example service I'm going to use a service called the Country Info Service.  The service provides information like capital city name, currency, language spoken about countries keyed off of their ISO codes.  I'll build up some virtual views of the service operations to show some possibilites for abstracting the services and highlight what the importer does for you, and what you have to do for yourself.


Here is a sample request and response from the service.


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="">
    <CapitalCity xmlns="">


<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="">
    <CapitalCityResponse xmlns="">


You could use the info from the Teiid Reference Guide and the wiki to manually create models to build and parse this XML.  They're pretty simple documents after all.  Or you could use the WSDL into Relational Source Model Importer to create them for you.  Not all service messages will be this simple after all, and the tooling is here to assist you in your work.


The first step for using the WSD into Relational Importer is to create a new Web Service Data Source Connection Profile.

Click File->New->Other to open the New Wizard Dialog, select Connection Profile from within the Connection Profiles folder, and click Next.  Select Web Services Data Source from the list of connection profiles and provide a name and an optional desciption for the profile.  Click Next.





For our purposes all we need to supply in the profile is the URL to the WSDL.  Paste the following URL into the WSDL URL or Location edit box and click Finish.





With our profile defined we can create a new Teiid Model Project and use the importer to create some models.  If you're not already there open the Teiid Designer perspective by clicking Window->Open Perspective->Teiid Designer (or Other and select from the list if it's not displayed in the pop-up).  Then create a new Teiid Model Project by clicking New->Teiiid Model Project.  Give the project a name like CountryInfo and click Finish.




Now right click on the new project and select Import from the pop-up menu.  Open the Teiid Designer folder in the Import Wizard dialog, select WSDL into Relational Source Model, and click Next.  On the displayed Source and Target Selection Page, click on the Connection Profile combo-box to display the configured Web Service Data Source connection profiles, and select the Country Info Service profile we just created.  Then click the Validate WSDL button to validate that the WSDL is WSI compliant.  The Location text box should already be filled in the the project name because we invoked the wizard by right clicking on the project, if not, you can browse and select the project.  Click Next.




The WSDL Operations Selection page displays the services, ports, bindings, and operations defined by the WSDL.  In this case we can see that there is a single service (CountryInfoService) defining a single port (CountryInfoServiceSoap) defining a single binding (CountryInfoServiceSoapBinding) defining a number or operations.  For the sake of simplicity, I'm going to deselect all operations except CapitalCity and FullCountryInfo.  Click Finish to generate the models.




Let's take a look at what the importer generates for this service.


Expanding the nodes in the model explorer you can see that the importer creates one physical model called CountryInfoService with a single procedure definition called invoke( ).  This procedure is the physical model for the CountryInfoServiceSoapBinding binding, and will be bound to a web service translator and data source.




The importer also created a virtual model called CountryInfoServiceXML.  This model contains virtual procedures that are capable building the XML elements the operations expect as request documents from scalar values, and virtual procedures that can convert the response documents returned from the operations into tables.  The procedures accomplish this vis the new SQL/XML functions offered by Teiid.  We'll take a look at the functions created in the CapitalCity schema to see an example of what the importer creates to build and parse the XML.  The CapitalCity operation takes a CapitalCity element containing a sCountryISOCode element of type String as a request, and returns a CapitalCityResponse element containing a CaptalCityResult of type String as a response.  The importer has created for us in this case a create_CapitalCity procedure and an extract_CapitalCityResponse procedure to build and parse the required documents.





Drilling into the transformation diagrams for the procedures we can view their implementation.  The XMLELEMENT function is used in the create_CapitalCity procedure, which reads like this:


          NAME CapitalCity, 
          XMLELEMENT(NAME sCountryISOCode, 
     AS xml_out;


The XMLTABLE is used in the extract_CapitalCityResponse to create the procedure result, which reads like this:


                    COLUMNS CapitalCityResult string) 
     AS t;


These procedures are about as simple as it gets and offer a good starting point to explore how we invoke the service.  We'll return here in part II of this post.  First we will look at a slightly more complex XML transformation.


In the FullCountryInfo schema there are three procedures, one with the create_ prefix and two with the extract_ prefix.  Like the CapitalCity operation, the FullCountryInfo operation takes a document with a single string element of sCountryISOCode as a request, so the create_FullCountryInfo procedure is nearly identicle to the create_CapitalCity procedure.  The FullCountryInfoResponse is more complex, it contains several scalar values as well as a complex type (tLanguage) with multiplicity.  In order to parse this document into scalar values the importer creates two procedures.  Here's an example of the response:


<?xml version="1.0" encoding="utf-8"?>


The extract_FullCountryInfo procedure extracts the string scalar values for ISOCode, Name, CapitalCity, etc from the response using the XMLTABLE function.


                              sISOCode string, 
                              sName string, 
                              sCapitalCity string, 
                              sPhoneCode string, 
                              sContinentCode string, 
                              sCurrencyISOCode string, 
                              sCountryFlag string) 
     AS t;


The extract_tLanguage procedure extracts the ISOCode and Name string values from the tLanguage elements contained in the response.  Here's how it reads:


                              sISOCode string, 
                              sName string) 
     AS t;


It's important to note here that the importer creates procedures that build or parse all of the values for the xml document,  This may be more than you want or need in your transformation.  Perhaps you're only interested in the phone codes.  In that case there's no need to parse out all of the values from the XML.  Or perhaps a request declaration in a WSDL has many optional parameters that you know you will not use in your VDB.  There's no need to model those paramters in your procedure.  You can edit the transformation created by the importer to build or parse the XML document you want.  You're in control.


In the next part of this post, I'll show you how to execute these services.


Teiid 7.0 Final

Posted by jdoyle Jun 30, 2010

I've just returned from 2 weeks vacation to get the news that Teiid 7.0-Final has been released.  Great work Steve and Ramesh.


Teiid 7.0 CR1 Announced

Posted by jdoyle Jun 11, 2010

Teiid 7.0 CR1 was announced this morning and you should go and take a look at it right away if you have ever used, or even thought about using Teiid, or are a hsitorical MetaMatrix user.  In my opinion, the list of feature improvements that Steve and Ramesh have have fit into this release are ASTOUNDING.  Take a look at the list of high level changes in this post.


Awsome work guys, I'm looking forward to building on top of this improved foundation.