Появилась у нас задача забирать данные через SOAP из 1C, а потом и через простой REST из Яндекс-Метрики и Google-Analitycs. Путей решения наметилось три:
- Написать .NET-приложение или скрипт PowerShell/JS/VBS, который бы забирал XML с сервиса, парсил и сохранял в таблицу SQL, либо просто в файл
- Написать CLR-процедуру, которая бы возвращала XML с сервиса
- Обойтись средствами 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'
