Connect to a web service from SQL

Question

SQL Server can open excel worksheets (xlsx), access databases (mdb), and other data streams using data providers (such as JET, ACE) and OPENROWSET.

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

I am thinking of something like:

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

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

thanks

Answer 1

You can use the SQL-CLR assembly (in versions 2005 or later) to process a stored procedure call and insert data into a SQL Server database table.

See some of these guides (much more when you're Google or Bing for it):

• CLR Stored Procedure Calling an External Web Service - SQL Server 2005 Tutorials
• Using the Web Service from the SQL Server 2005 CLR Assembly
• Request a web service using SQLCLR
• Calling a web service from an SQLCLR stored procedure
• Calling a web service from SQL Server
• How to use a web service from SQL Server using SQL CLR