I have a stored procedure in SQL Server that is connecting to a URL and parsing xml into tables. The URL is on a network that I can access via a VPN connection, but instead of connecting the computer to the network can you just login using SQL Server or tsql.
This is the command:
ePro2 @url = 'http://***.***.***.***//cgi-bin/livedataxml.cgi'
,@http_basic_username = '****'
,@http_basic_password = '********'
The username and password is used to access the URL itself not the domain network too.
This is how I connect to the URL from within the stored procedure:
exec @ole_result = sp_OACreate 'MSXML2.XMLHttp', @ole_object OUT
exec @ole_result = sp_OAMethod @ole_object, 'open', NULL, 'GET', @url, false, @http_basic_username, @http_basic_password
exec @ole_result = sp_OAMethod @ole_object, 'send', NULL, ''
exec @ole_result = sp_OAGetProperty @ole_object, 'status', @http_status OUT
exec @ole_result = sp_OAGetProperty @ole_object, 'responseXML.xml', @http_response OUT
SET @xml = @http_response