Connect to a webservice from SQL

sql-server web-services openrowset
Wadih M. picture Wadih M. · Nov 30, 2011 · Viewed 11.3k times · Source

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

marc_s picture marc_s · Nov 30, 2011

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):

Related questions

Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'

I have created a web service which is saving some data into to db. But I am getting this error: Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'. My connection string …

Read More
Inserting Data into SQL Server from variables via SSIS

I have consumed simple web service for addition of two numbers and I am getting the result in xml format that I have stored in variable supposed named as my_data, now I have to insert that result in my …

Read More
Call web service from SQL CLR?

I have a SQL Server 2012 stored procedure that returns a table. I have to modify that SP to add an additional value to the returned table. Unfortunately, that added value comes from a call to a web-service. From my research, …

Read More