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="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <CapitalCity xmlns="http://www.oorsprong.org/websamples.countryinfo"> <sCountryISOCode>US</sCountryISOCode> </CapitalCity> </soap:Body> </soap:Envelope>
<?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <CapitalCityResponse xmlns="http://www.oorsprong.org/websamples.countryinfo"> <CapitalCityResult>Washington</CapitalCityResult> </CapitalCityResponse> </soap:Body> </soap:Envelope>
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:
CREATE VIRTUAL PROCEDURE BEGIN SELECT XMLELEMENT( NAME CapitalCity, XMLNAMESPACES(DEFAULT 'http://www.oorsprong.org/websamples.countryinfo'), XMLELEMENT(NAME sCountryISOCode, COUNTRYINFOSERVICEXML.CAPITALCITY.CREATE_CAPITALCITY.sCountryISOCode)) AS xml_out; END
The XMLTABLE is used in the extract_CapitalCityResponse to create the procedure result, which reads like this:
CREATE VIRTUAL PROCEDURE BEGIN SELECT t.* FROM XMLTABLE( XMLNAMESPACES(DEFAULT 'http://www.oorsprong.org/websamples.countryinfo'), '/CapitalCityResponse' PASSING COUNTRYINFOSERVICEXML.CAPITALCITY.EXTRACT_CAPITALCITYRESPONSE.xml_in COLUMNS CapitalCityResult string) AS t; END
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"?> <tCountryInfo> <sISOCode>BE</sISOCode> <sName>Belgium</sName> <sCapitalCity>Brussels</sCapitalCity> <sPhoneCode>32</sPhoneCode> <sContinentCode>EU</sContinentCode> <sCurrencyISOCode>EUR</sCurrencyISOCode> <sCountryFlag>http://www.oorsprong.org/WebSamples.CountryInfo/Images/Belgium.jpg</sCountryFlag> <Languages> <tLanguage> <sISOCode>nl</sISOCode> <sName>Dutch</sName> </tLanguage> </Languages> </tCountryInfo>
The extract_FullCountryInfo procedure extracts the string scalar values for ISOCode, Name, CapitalCity, etc from the response using the XMLTABLE function.
CREATE VIRTUAL PROCEDURE BEGIN SELECT t.* FROM XMLTABLE( XMLNAMESPACES(DEFAULT 'http://www.oorsprong.org/websamples.countryinfo'), '/FullCountryInfoResponse/FullCountryInfoResult/Languages' PASSING COUNTRYINFOSERVICEXML.FULLCOUNTRYINFO.EXTRACT_FULLCOUNTRYINFORESPONSE.xml_in COLUMNS sISOCode string, sName string, sCapitalCity string, sPhoneCode string, sContinentCode string, sCurrencyISOCode string, sCountryFlag string) AS t; END
The extract_tLanguage procedure extracts the ISOCode and Name string values from the tLanguage elements contained in the response. Here's how it reads:
CREATE VIRTUAL PROCEDURE BEGIN SELECT t.* FROM XMLTABLE( XMLNAMESPACES(DEFAULT 'http://www.oorsprong.org/websamples.countryinfo'), '/FullCountryInfoResponse/FullCountryInfoResult/Languages/tLanguage' PASSING COUNTRYINFOSERVICEXML.FULLCOUNTRYINFO.EXTRACT_TLANGUAGE.xml_in COLUMNS sISOCode string, sName string) AS t; END
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.