http post with BodyRequest Sqlserver

0

Hello, how do I get a post request passing an xml as date? I tried it on another GET request and it worked:

        DECLARE @Tabela TABLE ( CampoXML XML);

        DECLARE @URL VARCHAR(8000) 
        SELECT @URL = 'url?parametros='
        DECLARE @Response varchar(8000)
        DECLARE @XML xml
        DECLARE @Obj int 
        DECLARE @Result int 
        DECLARE @HTTPStatus int 
        DECLARE @ErrorMsg varchar(MAX)
        EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
        EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
        EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
        EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
        EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

        INSERT @Tabela ( CampoXML )
        EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

        SELECT * FROM @Tabela

however I need to do a post request, and go through body xml below:

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <acertaContratoEntrada xmlns="http://...">
   <usuario>usuario</usuario>
   <senha>senha</senha>
 </acertaContratoEntrada>

Thank you for your response.

    
asked by anonymous 02.02.2017 / 13:00

1 answer

0

I'll leave the solution here, I was able to do the POST request passing data through the body by sqlserver with the code below:

-- Informação a enviar pelo body
DECLARE @BODY_XML VARCHAR(8000) = 
' <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <acertaContratoEntrada xmlns="http://...">
    <usuario>usuario</usuario>
    <senha>senha</senha>
    </acertaContratoEntrada>'


DECLARE @Tabela TABLE ( CampoXML XML);

DECLARE @URL VARCHAR(8000) 
SELECT @URL = 'http://minha-url'
DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg varchar(MAX)
EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'POST', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/xml' -- ALTERAÇÃO NECESSARIA PARA FUNCIONAR
EXEC @Result = sp_OAMethod @Obj, send, NULL, @BODY_XML
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

-- ================ INSERE NA TABELA TEMPORARIA
INSERT @Tabela ( CampoXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

select * from @Tabela
    
02.02.2017 / 13:32