Web Services via SQL

I'm a bit tied up coding these days, so enjoy this guest post from Senior SE Kam Thakker. -- S.P.

Happy New Year to all of our EII Files readers in the States. At the beginning of a new year, it’s only natural to think of new possibilities and how we may be able to attempt and implement new and different ideas. For those of you  that are SQL programmers or XQuery programmers, new possibilities may take the form of meshing previously separate worlds, but still working within the data models with which we’re familiar and ultra-productive.

Srinivas in earlier blog entries, has illustrated many techniques how various EII innovations can help us move forward.

As new types of data sources, such as Web Services data, become necessary to integrate to expose to our end-user customers, how can we perform efficient queries within the comfortable data models we know, without compromising fidelity of the data source and without dampening our productivity...?

Let me describe for you, an interesting new innovation called Web Services Tables at Ipedo and get your thoughts.

Being able to expose relational data as a web service is quite useful as SOA projects grow and full-featured EII systems let you do this...but what about the inverse?

On this front, given the ubiquity of SQL and the proliferation of Web Services, we felt that there must be a way to innovate to allow a big boost in productivity for EII architects. While XQuery is gaining momentum (as Tim pointed to Gartner’s comments in today’s Integration Insider entry) and is the easy choice to query WSDL files and their underlying methods, what about those with SQL expertise and haven’t ramped up on XQuery yet?

Our epiphany became XML Table Views, which elegantly allow XML-based WSDLs to be mapped to a relational table, and then be queried via SQL just like any other native relational data source. Let me walk you through how we’ve designed this new WSDL/SQL mapping capability into the Ipedo Administration Console.

Ipedo XIP creates a virtual relational table based on the schema described in a WSDL file. A client application accesses the virtual relational table using standard SQL, and Ipedo XIP manages the invocation of the underlying Web Service dynamically. This allows business intelligence and reporting tools, or any other SQL-based client application to consume Web Services data as if it were relational data; including joins across multiple Web Services, or combinations of Web Services and existing relational databases.

The Web Services Tables mapping process starts with creating an XML Table. The Ipedo View Wizard supplies an intuitive GUI to map an XML tree to a relational table.

The GUI XML Table Wizard is made up of a standard wizard framework consisting of a number of intelligent mapping processes.

After additional wizard screens, GUI “XML node-to-relational table” mapping is illustrated here, which then automatically generate SQL code for you.

I hope this gives you a flavor of how this new functionality works, mapping WSDL files to relational tables to allow querying via SQL. And within the context of your EII application,  these same SQL routines can then be used to query other federated relational data sources from within the Ipedo View construct...

If this is intriguing and you have further questions, please reach us and we’d be happy to go into more detail. We have screencasts available in our Demo Zone and you can inquire with us directly at blogs (at) ipedo-dot-com.

We’re finding many organizations have been quite interested in this new functionality and how it may fit into their current and future EII projects...

What a new year it will be...