SQL Server 2008 R2’de T-SQL ile Web Service İşlemleri

Arkadaşlar Merhaba,

Bu yazımda sizlere SQL Server üzerinde T-SQL ile nasıl web service işlemleri yapabileceğimizi anlatacağım.

Kimi zaman bazı entegrasyon işlerinde web service ile ilgili işlemlerimiz için C# üzerinde kod yazmak gerekir ve hem zaman alır hem de can sıkıcı bir durum ortaya çıkar.

Bunun yerine bu işi T-SQL ile de kolayca çözebiliriz. İster web servis üzerinden veri alabilir istersek de veri gönderebiliriz.

Bununla ilgili olarak Vishal Seth adlı arkadaş bir stored procedure geliştirmiş. Ancak bu kodu direk çalıştırdığımızda bir takım hatalar veriyordu ve kodu düzeltmek durumunda kaldık. Şu an için SQL Server 2008 R2’de çalışıyor. Ancak SQL Server 2014 üzerinde çalışmıyor. SQL Server 2014 üzerinde sorunu çözdüğümüzde ilerleyen günlerde sizlerle burada paylaşacağım.

Kodu ilk defa çalıştırdığınızda izinlerle ilgili bir takım hatalar alabilirsiniz. İzinleri verdikten sonra kod çalışacaktır.

Aşağıdaki T-SQL kodu ile Stored Procedure’nüzü oluşturabilirsiniz.

CREATE Proc [dbo].[ebs_GetWebServiceXML]
(
@URI varchar(2000) = '',
@methodName varchar(50) = 'POST',
@requestBody varchar(max),
@SoapAction varchar(255),
@UserName varchar(255)='',
@Password varchar(255)='',
@resultXML XML output
)
as

declare @envelope nvarchar(max), @responseText varchar(100), @Result int

IF @methodName = ''

BEGIN

 select FailPoint = 'Method Name must be set'

 return

END

set @responseText = 'FAILED'

DECLARE @objectID int

DECLARE @hResult int

DECLARE @source varchar(255), @desc varchar(255)

EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Create failed',

MedthodName = @methodName

goto destroy

return

END

-- open the destination URI with Specified method

EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Open failed',

MedthodName = @methodName

goto destroy

return

END

--EXEC sp_OASetProperty @objectID, 'setTimeouts',resolveTimeout,connectTimeout,sendTimeout,receiveTimeout

EXEC sp_OASetProperty @objectID, 'setTimeouts','5000000','5000000','5000000','5000000'

-- set request headers

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @methodName

goto destroy

return

END

-- set soap action

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @methodName

goto destroy

return

END

declare @len int

set @len = len(@requestBody)

EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'SetRequestHeader failed',

MedthodName = @methodName

goto destroy

return

END

-- send the request

EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody

IF @hResult <> 0

BEGIN

EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT

SELECT hResult = convert(varbinary(4), @hResult),

source = @source,

description = @desc,

FailPoint = 'Send failed',

MedthodName = @methodName

goto destroy

return

END

declare @statusText varchar(1000), @status varchar(1000)

-- Get status text

exec sp_OAGetProperty @objectID, 'StatusText', @statusText out

exec sp_OAGetProperty @objectID, 'Status', @status out

IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml

CREATE TABLE #xml ( yourXML XML )

INSERT #xml ( yourXML )

EXEC @Result = sp_OAGetProperty @objectID, 'responseXML.xml'--, @resultXML OUT

SELECT @resultXML=yourXML

FROM #xml

destroy:

exec sp_OADestroy @objectID

Peki kodu nasıl kullanacağız? Bununla ilgili olarak ben örnek bir web service hazırladım.

Örnek Web Service

Örnek Web Service

Şimdi burada Web Service’deki GetData metodunu kullanarak verileri çekeceğiz ve SQL Server’da tabloda göstereceğiz. Öncelikle web service’e bağlanıp verileri almamız gerekiyor. Bunun için yukarıda yazmış olduğumuz stored procedure’i kullanacağız. Stored Procedure’ı çalıştırınca XML olarak bize cevap dönecek.

declare
      @URI varchar(2000) = 'http://10.1.1.241/webservice.asmx',
      @methodName varchar(50) = 'POST',
      @requestBody varchar(8000) = '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                                    <soap:Body>
                                    <GetData xmlns="http://10.1.1.241" />
                                    </soap:Body>
                                    </soap:Envelope>',
      @SoapAction varchar(255)='http://10.1.1.241/GetData',
      @UserName nvarchar(100)='UserName',
      @Password nvarchar(100)='PassWord',
      @XMLresult XML,
      @XMLnew XML

exec ebs_GetWebServiceXML @URI,@methodName,@requestBody,@SoapAction,@UserName,@Password,@XMLresult output

Gördüğünüz gibi output olarak yani cevap olarak bize xml bir sonuç dönüyor.

Web Service'ten Dönen XML Sonuç

Web Service’ten Dönen XML Sonuç

Peki bu XML sonucu nasıl sql tablosu’nda göstereceğiz? Aşağıdaki örnek kod ile XML’i tablo olarak gösterebilirsiniz.

SELECT
T.N.value('HISSE[1]','nvarchar(5)') as 'HISSE',
T.N.value('TARIH[1]','date') as 'TARIH',
T.N.value('FIYAT[1]','float') as 'FIYAT'
FROM @XMLresult.nodes('//Data') as T(N)
XML to SQL Table

XML to SQL Table

Tebrikler, başardınız 🙂 İyi çalışmalar…

Bir Cevap Yazın