How to run a WSDL Webservice from a SQL Server Stored Procedure

Asked

Viewed 372 times

0

Does anyone know how to create an SQL Server database to run a stream Webservice ?

The idea is to create a process from the execution of Procedure

I read about the Ole Automation Stored Procedures of SQL Server but I’m having doubts about how to assemble them because I need to pass the URL 

http://SERVIDOR/webdesk/ECMWorkflowEngineService?wsdl'

and XML 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://ws.workflow.ecm.technology.totvs.com/">
   <soapenv:Header/>
   <soapenv:Body>
      <ws:simpleStartProcess>
         <username>usuario</username>
         <password>senha</password>
         <companyId>1</companyId>
         <processId>processo</processId>
         <comments>GERADO VIA WS</comments>
         <attachments>
            <!--Zero or more repetitions:-->
            <item>
               <attachmentSequence>?</attachmentSequence>
               <!--Zero or more repetitions:-->
               <attachments>
                  <!--Optional:-->
                  <attach>?</attach>
                  <!--Optional:-->
                  <descriptor>?</descriptor>
                  <!--Optional:-->
                  <editing>?</editing>
                  <!--Optional:-->
                  <fileName>?</fileName>
                  <!--Optional:-->
                  <fileSelected/>
                  <fileSize>?</fileSize>
                  <!--Optional:-->
                  <filecontent>cid:702686015833</filecontent>
                  <!--Optional:-->
                  <fullPatch>?</fullPatch>
                  <!--Optional:-->
                  <iconPath>?</iconPath>
                  <!--Optional:-->
                  <mobile>?</mobile>
                  <!--Optional:-->
                  <pathName>?</pathName>
                  <!--Optional:-->
                  <principal>?</principal>
               </attachments>
               <!--Optional:-->
               <colleagueId>?</colleagueId>
               <!--Optional:-->
               <colleagueName>?</colleagueName>
               <companyId>?</companyId>
               <!--Optional:-->
               <crc>?</crc>
               <!--Optional:-->
               <createDate>?</createDate>
               <!--Optional:-->
               <createDateTimestamp>?</createDateTimestamp>
               <!--Optional:-->
               <deleted>?</deleted>
               <!--Optional:-->
               <description>?</description>
               <!--Optional:-->
               <documentId>?</documentId>
               <!--Optional:-->
               <documentType>?</documentType>
               <!--Optional:-->
               <fileName>?</fileName>
               <!--Optional:-->
               <newAttach>?</newAttach>
               <!--Optional:-->
               <originalMovementSequence>?</originalMovementSequence>
               <!--Optional:-->
               <permission>?</permission>
               <processInstanceId>?</processInstanceId>
               <size>?</size>
               <!--Optional:-->
               <version>?</version>
            </item>
         </attachments>
         <cardData>
            <!--Zero or more repetitions:-->
            <item>
               <!--Zero or more repetitions:-->
               <item>codigoProduto</item>
               <item>"Item"</item>
            </item>
         </cardData>
      </ws:simpleStartProcess>
   </soapenv:Body>
</soapenv:Envelope>

Does anyone have any idea how to do that ?

sql

1 answer

0

Maybe it is not a better method to call a WSDL, but I believe that the example below should work for you:

DECLARE @obj int,
        @url VarChar(MAX),
        @response VarChar(MAX),
        @requestHeader VarChar(MAX),
        @requestBody VarChar(MAX)

SET @url = 'http://....'

SET @requestBody = '<soapenv:Envelope>
                     <soapenv:Header/>
                      <soapenv:Body>
                       ...
                      </soapenv:Body>
                     </soapenv:Envelope>'

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, false
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'text/xml;charset=UTF-8'
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'SOAPAction', 'POST'
EXEC sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Length', LEN(@requestBody)
EXEC sp_OAMethod @obj, 'send', NULL, @requestBody
EXEC sp_OAGetProperty @obj, 'responseText', @response OUT


SELECT @response [RESPONSE]

EXEC sp_OADestroy @obj

Here on this link Stackoverflow, you can find other alternatives and adapt to your problem.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.