CODEWITHSUNDEEP
sqlsql-server-2008
nkota
nkota

Reputation: 683

how to Call web service using stored procedure which returns json and store data in table using sql server?

how to Call web service using stored procedure which returns json and store data in table using sql server?

  ==============
  Declare @Object as Int;
  Declare @ResponseText as Varchar(8000);

--Code Snippet
 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
 Exec sp_OAMethod @Object, 'open', NULL, 'get',
             'WEB SERVICE LINK', --Your Web Service Url (invoked)
             'false'
 Exec sp_OAMethod @Object, 'send'
 Exec sp_OAGetProperty@Object, 'responseText', @ResponseText OUTPUT

 Select @ResponseText

 Exec sp_OADestroy @Object

Upvotes: 2

Views: 2325

Answers (1)

nkota
nkota

Reputation: 683

DECLARE @Object AS INT ;
            DECLARE @ResponseText AS VARCHAR(8000) ;
            DECLARE @Url AS VARCHAR(1000) ;
            SET @Url = 'Web service'

            EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT ; --
            EXEC sp_OAMethod @Object, 'open', NULL, 'get', @Url,'false'           
            EXEC sp_OAMethod @Object, 'send'
            EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

            SELECT  @ResponseText

            EXEC sp_OADestroy @Object

            DECLARE @handle INT 
            DECLARE @PrepareXmlStatus INT  


            EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT,
                @ResponseText

Upvotes: 1

Related Questions