Connect to a webservice from SQL

Question

SQL Server is able to open excel sheets (xlsx), access databases (mdb) and other data streams using data providers (e.g. JET, ACE) and OPENROWSET.

Are there similar facilities to extract data from a remote webservice ? Using OPENROWSET and providing it a web service driver and URL and some schema definition to translate the hierarchical nature of the webservice XML output into a table that SQL Server can query on.

I'm thinking of something like:

select * from 
OPENROWSET('WEBSERVICE.4.0.PROVIDER','http://mydomain/webservice.asmx')

That's a high level question, although I know it's conceptually possible, I'd like to know if there are any implementations of this idea.

Thanks

Answer 1

You could use a SQL-CLR assembly (in versions 2005 or newer) to handle the calling of the stored procedure, and to insert the data into your SQL Server database table.

See some of these tutorials (plenty more when you Google or Bing for it):

• CLR Stored Procedure Calling External Web Service - SQL Server 2005 Tutorials
• Consuming a Web Service from a SQL Server 2005 CLR Assembly
• Query a web service with SQLCLR
• Invoking a Web Service from a SQLCLR Stored Procedure
• Calling a Web Service from within SQL Server
• How to consume a web service from within SQL Server using SQL CLR