Учим SQL Server забирать данные с веб-сервисов (REST и SOAP)

Появилась у нас задача забирать данные через SOAP из 1C, а потом и через простой REST из Яндекс-Метрики и Google-Analitycs. Путей решения наметилось три:

1. Написать .NET-приложение или скрипт PowerShell/JS/VBS, который бы забирал XML с сервиса, парсил и сохранял в таблицу SQL, либо просто в файл
2. Написать CLR-процедуру, которая бы возвращала XML с сервиса
3. Обойтись средствами SQL, используя готовую COM-библиотеку MSXML2.ServerXMLHTTP через процедуры sys.sp_OA*

Победил третий вариант, как наиболее гибкий. 

В итоге получение данных с REST-вебсервиса через HTTP-GET выглядит так (голый код, без мишуры вроде обработки ошибок и логирования, на примере Яндекс-Метрики http://api.yandex.ru/metrika/):

-- Начальные данные: URL

declare 
    @OLE int,
    @URL nvarchar(max)='http://api-metrika.yandex.ru/counters?oauth_token=05dd3dd84ff948fdae2bc4fb91f13e22'

-- Выполняем запрос с помощью MSXML2.ServerXMLHTTP

exec sys.sp_OACreate 'MSXML2.ServerXMLHTTP',@OLE out 
exec sys.sp_OAMethod @OLE,'open',NULL,'GET',@URL,'false' 
exec sys.sp_OAMethod @OLE,'send' 

-- Получаем данные которые вернул сервер

declare @r table (r nvarchar(max)) 
insert @r exec sys.sp_OAGetProperty @OLE,'responsetext' 

-- Удаляем объект

exec sys.sp_OADestroy @OLE

Для SOAP чуть посложнее, но смысл тот же (на примере простого запроса к demo-серверу http://www.guru4.net/):

-- Начальные данные: namespace, URL, заголовок и тело метода

declare @URL nvarchar(max),@Header XML,@Body XML
;with xmlnamespaces (default 'http://www.guru4.net')
select
    @URL='http://www.guru4.net/articoli/javascript-soap-client/demo/webservicedemo.asmx',
    @Body=(select '' for xml path('HelloWorld'),type)

-- Строим запрос

declare @SOAPAction nvarchar(max),@OLE Int,@Request xml
;with xmlnamespaces ('http://www.w3.org/2001/XMLSchema-instance' as [xsi],'http://www.w3.org/2001/XMLSchema' as [xsd],'http://schemas.xmlsoap.org/soap/envelope/' as [soap])
select
    @SOAPAction=@Body.value('namespace-uri(/*[1])','SysName')+'/'+@Body.value('local-name(/*[1])','SysName'),
    @Request=(select @Header as [soap:Header],@Body as [soap:Body] for xml path('soap:Envelope'),type)

-- Выполняем запрос с помощью MSXML2.ServerXMLHTTP

exec sys.sp_OACreate 'MSXML2.ServerXMLHTTP',@OLE OUT
exec sys.sp_OAMethod @OLE,'open',NULL,'POST',@URL,'false'
exec sys.sp_OAMethod @OLE,'setRequestHeader',NULL,'Content-Type','text/xml; charset=utf-8'
exec sys.sp_OAMethod @OLE,'setRequestHeader',NULL,'SOAPAction',@SOAPAction
exec sys.sp_OAMethod @OLE,'send',NULL,@Request

-- Результат запроса

declare @r table (r nvarchar(max))
insert @r exec sys.sp_OAGetProperty @OLE,'responsetext'

-- Удаляем объект

exec sys.sp_OADestroy @OLE

Вариант хорош тем, что понятен любому SQL-разработчику и не требует дополнительных знаний .NET

UPD 13/05/2014

Если SOAP-метод веб-сервиса требует параметры, они передаются в запросе:

@Body=(select 'Значение1' as Param1,'Значение2' as Param2 for xml path('HelloWorld'),type)

Важно! Имена параметров чувствительны к регистру

UPD 16/04/2015

Если на веб-сервисе включена аутентификация (Basic или Integrated Windows), то в методе Open можно передать логин и пароль:

exec sys.sp_OAMethod @OLE,'open',NULL,'POST',@URL,'false','LOGIN','PASSWORD'