Consuming webservice per SQL Server procedure?

0

I'm using sql server 2016 to consume webservice per store procedure. I have a webservice in JSON returning the following:

[{"nome":"Rebecca","email":"[email protected]","ra":"12345"},{"nome":"Caroline","email":"[email protected]","ra":"23456"},{"nome":"Vanessa","email":"[email protected]","ra":"99999"}]

I can consume it by passing a variable. If I leave my webservice to receive a parameter and pass it I can return the student's name and email. But if I leave my webservice without the need to receive parameter and remove the procedure parameter pass and execute, my procedure returns null. What do I need to change in my code to be able to return the complete list of students my webservice exposes?

Here is my code with parameter passing that the corresponding RA user returns:

create PROCEDURE webservice_parametros (@RA as varchar (5))
AS
BEGIN

DECLARE @OBJ INT;
DECLARE @URL VARCHAR(200);
DECLARE @RESPONSE VARCHAR(8000);

SET @URL = 'http://dominio:8080/v1/alunos/' + @RA
EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
EXEC SP_OAMETHOD @OBJ, 'SEND'
exec SP_OAGETPROPERTY @OBJ, 'responseText', @RESPONSE out
EXEC SP_OADESTROY @OBJ

SELECT JSON_VALUE(@RESPONSE, '$.nome') as nome,
    JSON_VALUE(@RESPONSE, '$.email') as email

END     

execute webservice_parametros '12345'

Edited

My code that returns null for a service that does not need parameters

create PROCEDURE webservice
AS
BEGIN

DECLARE @OBJ INT;
DECLARE @URL VARCHAR(200);
DECLARE @RESPONSE VARCHAR(8000);

SET @URL = 'http://dominio:8080/v1/alunos/'
EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
EXEC SP_OAMETHOD @OBJ, 'SEND'
exec SP_OAGETPROPERTY @OBJ, 'responseText', @RESPONSE out
EXEC SP_OADESTROY @OBJ

SELECT JSON_VALUE(@RESPONSE, '$.nome') as nome,
    JSON_VALUE(@RESPONSE, '$.email') as email

END     

execute webservice

Edited

Here is a public web service that I tested and returned null in the same way.

http://ws.sistemaindustria.org.br/api-basi/v1/entidades/3/departamentos.json

    
asked by anonymous 21.07.2017 / 13:55

1 answer

0

Here's the answer:

DECLARE @OBJ INT;
DECLARE @URL VARCHAR(200);
DECLARE @RESPONSE VARCHAR(8000);

CREATE TABLE #xml (
Ds_Dados VARCHAR(MAX)
)

SET @URL = 'http://ws.sistemaindustria.org.br/api-basi/v1/entidades/3/departamentos.json'
EXEC SP_OACREATE 'MSXML2.ServerXMLHttp', @OBJ out
EXEC SP_OAMETHOD @OBJ, 'OPEN', NULL, 'GET', @URL, FALSE
EXEC SP_OAMETHOD @OBJ, 'SEND'
insert into #xml
exec SP_OAGETPROPERTY @OBJ, 'responseText'
EXEC SP_OADESTROY @OBJ

select * from #xml
    
05.10.2017 / 17:53