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

Schematic

Not directly, but if you create a custom driver to encapsulate the service, then yes. The link server functionality that SFDC offers is a good example. From SSIS you can work directly with web services. – Indaba

Or you could create a SQL-CLR assembly that calls the external web service, and deploy that into SQL Server (version 2005 and newer) – Drawers

@marc_s: that sounds interesting, could you please give me some more details ? – Schematic

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

Drawers