How to call a restful api with mssql and update the data on its table/cell03.02.21

DECLARE @return as nvarchar(max)
DECLARE @status int
DECLARE @responseText as table(responseText nvarchar(max))
DECLARE @res as Int;
DECLARE @url as nvarchar(1000) = 'http://api3.salamander-jewelry.net/cimco/stock_tools_and_materials?f=xml'
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @res OUT
EXEC sp_OAMethod @res, 'open', NULL, 'GET',@url,'false'
EXEC sp_OAMethod @res, 'send'
EXEC sp_OAGetProperty @res, 'status', @status OUT
INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @res, 'responseText'
EXEC sp_OADestroy @res

DECLARE @XMLToParse  XML
SET	@XMLToParse = CAST((SELECT TOP 1 responseText FROM @responseText) AS varchar(max))
DECLARE	@ParsingTable  TABLE (syn	VARCHAR(100))
INSERT INTO @ParsingTable(syn)
SELECT xmlData.A.value('.', 'VARCHAR(100)') AS syn
FROM @XMLToParse.nodes('root/data') xmlData(A)

SELECT @return = (SELECT top 1 syn FROM @ParsingTable)
select @return

UPDATE [SL_h].[dbo].[reports]
SET stock_tools_and_materials = @return
WHERE YEAR([date]) = YEAR(GETDATE())
AND MONTH([date]) = MONTH(GETDATE())
AND DAY([date]) = DAY(GETDATE())
AND stock_tools_and_materials = 0

Anonymous

How to call a restful api with mssql and update the data on its table/cell03.02.21

Information Epoch 1713519579