Forum Discussion

malikjaved80's avatar
malikjaved80
New Contributor
7 years ago

setting soap request properties from T-sql

Below is the code I am using to connect to my api server. All works fine for other calls, but when it comes to uploading csv files, it's doesn't work. If I copy the code into SoapUI and run from there, it does work but in SoapUI I have to change the request property "Enable inline files" to true. Does anyone knows how I can pass that property value in my below code?

 

declare 
@Url varchar(1024),
@HttpMethod varchar(10),
@ParamsValues varchar(1024), 
@SoapAction varchar(8000) 

DECLARE @t table (ID int, strxml xml)

set @Url = 'https://api.my.com/api.asmx'
set @HttpMethod = 'soap'
set @SoapAction = '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:apic="http://apiconn.com">
   <soap:Header/>
   <soap:Body>
      <apic:AddBook>
         <apic:addbookID>18</apic:addbookID>
         <apic:data>file:C:/temp/Book2.csv</apic:data>
         <apic:dataType>CSV</apic:dataType>
      </apic:AddBook>
   </soap:Body>
</soap:Envelope>'


if @HttpMethod in ('get','GET') and len(@ParamsValues) > 0
begin
    set @Url = @Url + '?' + @ParamsValues   
end

declare @obj int
    ,@response varchar(8000)
    ,@responseText varchar(8000)
    ,@status varchar(50)
    ,@statusText varchar(1024)
    ,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end)

--exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @obj out
exec sp_OACreate 'MSXML2.ServerXMLHTTP', @obj out
exec sp_OAMethod @obj, 'Open', null, @method, @Url, false

if @HttpMethod in ('get','GET')
begin
    exec sp_OAMethod @obj, 'send'
end
else if @HttpMethod in ('post','POST')
begin
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'application/x-www-form-urlencoded'
    exec sp_OAMethod @obj, 'send', null, @ParamsValues
end
else if @HttpMethod in ('soap','SOAP')
begin
    if @SoapAction is null
        raiserror('@SoapAction is null', 10, 1)

    declare @host varchar(1024) = @Url
    if @host like 'http://%'
        set @host = right(@host, len(@host) - 7)
    else if @host like 'https://%'
        set @host = right(@host, len(@host) - 8)

    if charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host)
        set @host = left(@host, charindex(':', @host) - 1)
    else 
        set @host = left(@host, charindex('/', @host) - 1)

    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'Host', @host
    exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction
    exec sp_OAMethod @obj, 'send', null, @SoapAction
end

Insert into @t (strxml) 
exec sp_OAGetProperty @obj, 'responseXML.xml' --, @responseText out
exec sp_OAGetProperty @obj, 'Status', @status out
exec sp_OADestroy @obj

declare @X xml
select @X = strxml from @t

SELECT @X
SELECT @status