I've got a project where I need to send an XML web request as x-www-form-urlencoded but I need to send it from a SQL Server stored procedure, and the XML has to be sent as a key value with the key name 'xml_in'. I've not done much web development before and this is the first time I'm sending a web request from SQL, so I have very little knowledge of sp_OAMethod to know how to specify the request.
Any help greatly appreciated.
Many thanks.
Kind regards,
Richard Twyning
What I have tried:
DECLARE @PostData VARCHAR(MAX) = 'xml_in=' + CONVERT(VARCHAR(MAX), @XML)
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);
-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
--EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader; --Rich.T. Authentication not required for TNT
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'User-Agent', 'Ship_socket/1.0'
--EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'xml_in', @XML
-- EXEC @ret = sp_OAMethod @token, 'send', NULL, NULL;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @PostData
-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;
-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;
-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);
SELECT @responseText