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