Get XML Response From Web URL

First set the following option in sql :
sp_configure 'show advanced option',1
reconfigure

Sp_configure 'Ole Automation Procedures',1
Reconfigure

Then after run the following script by passing your web URL to get the XML response.

DECLARE @Object as Int;
Declare @ResponseText as Varchar(8000);
Declare @Url as Varchar(MAX);
select @Url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @Url, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object

--load into Xml
Declare @XmlResponse as xml;


select @XmlResponse = CAST(@ResponseText as xml)
select @XmlResponse