Click here to Skip to main content
14,826,492 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 6-Oct-20 4:05am

See Send XML using application/x-www-form-urlencoded and key[^]. Perhaps the two of you should put your heads together and work out what needs to be done.
   
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP.3.0', @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, 'Content-type', 'application/x-www-form-urlencoded';

DECLARE @HTTP_Params varchar(2000) = ''
SET @HTTP_Params = 'xml_in='+@Content
EXEC @ret = sp_OAMethod @token, 'send', NULL, @HTTP_Params

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;

INSERT INTO @responseTable(ResponseText) EXEC @ret = sp_OAMethod @token, 'RESPONSETEXT'
IF @ret <> 0 BEGIN
EXEC sp_OAGetErrorInfo @token, @source OUT, @desc OUT

SELECT Error=-93, ErrMessage='Response (ResponseText) failed in '+@source+' with error: '+convert(varchar,@ret)+' '+@desc
END
SET @ResponseText = (SELECT TOP 1 ResponseText FROM @responseTable)

-- 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 @status as [Status],
@statusText AS [StatusText],
@responseText AS [ResponseText]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900