Connect to a webservice from SQL

2019-03-28 02:00发布

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

1条回答
Viruses.
2楼-- · 2019-03-28 02:31

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

查看更多
登录 后发表回答