Please find below an example of the code that will enable a stored procedure to draw upon XML or external data sources.

Prerequisite Server and Group Permissions Setup

Enable OLE Automation

USE master;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Grant Permissions to Stored Procedures on master to Group / User

USE master;
GO
GRANT EXEC ON sp_OACreate TO [DOMAIN\GROUP];
GRANT EXEC ON sp_OAMethod TO [DOMAIN\GROUP];
GRANT EXEC ON sp_OASetProperty TO [DOMAIN\GROUP];
GRANT EXEC ON sp_OAGetProperty TO [DOMAIN\GROUP];
GRANT EXEC ON sp_OADestroy TO [DOMAIN\GROUP];

Log Tables

Process Log

Please refer to the article about Scheduled Stored Procedures and Log Setup

JSON Format

Stored Procedure - ImportListJSON

Example JSON Output

{ "glossary": {
        "title": "example glossary",
		"GlossDiv": {
            "title": "S",
			"GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
					"SortAs": "SGML",
					"GlossTerm": "Standard Generalized Markup Language",
					"Acronym": "SGML",
					"Abbrev": "ISO 8879:1986",
					"GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
						"GlossSeeAlso": ["GML", "XML"]
                    },
					"GlossSee": "markup"
                }
            }
        }
    }
}

Source for JSON Text: https://json.org/example.html - I also used this schema extracted from this example throughout this page for JSON, XML and CSV.

dbo.ImportListJSON

CREATE PROCEDURE [dbo].[ImportListJSON]
AS
BEGIN

	DECLARE @URL VARCHAR(8000);
	DECLARE @Response VARCHAR(MAX);
	DECLARE @Obj int;
	DECLARE @Result int;
	DECLARE @HTTPStatus int;
	DECLARE @ErrorMessage varchar(8000);
	DECLARE @RowCount int;
	SET TEXTSIZE -1

	SET @URL = '<URL>';

	-- Create Temp Tables
	IF OBJECT_ID('tempdb..#Glossary', 'U') IS NOT NULL DROP TABLE #Glossary;
	CREATE TABLE #Glossary (
		ID varchar(20),
		SortAs varchar(20),
		GlossTerm varchar(200),
		Acronym varchar(20),
		Abbrev varchar(50)
    );

	IF OBJECT_ID('tempdb..#JSON') IS NOT NULL DROP TABLE #JSON;
	CREATE TABLE #JSON ( yourJSON nvarchar(max) );

	-- Process and import XML file into temp table
	TRUNCATE TABLE #JSON;
	
	EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Obj OUT;	-- Various sp_OACreate strings that may work if the current setting doesn't: 'MSXML2.ServerXMLHTTP' , 'MSXML2.XMLHTTP'
	-- EXEC @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'proxy.company.com:8080'; -- Proxy Server
	EXEC @Result = sp_OASetProperty @Obj, 'setTimeouts','120000','120000','120000','120000';
	
	EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false;
	EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Authorization', 'Bearer <TOKEN>';
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'Application/JSON';
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Accept', 'Application/JSON';
    EXEC @Result = sp_OAMethod @Obj, send, NULL,'';
	EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT;

 	PRINT CASE WHEN  @HTTPStatus = 200 THEN 'Connection Successful' ELSE 'Connection Failed' END; -- Display Connection Status Message

 	IF (@HTTPStatus = 200)
	BEGIN
		INSERT #JSON ( yourJSON )
		EXEC @Result = sp_OAGetProperty @Obj, 'ResponseText' --, @Response OUT
		;

		-- Data Retrieval Check
		SET @RowCount = (SELECT COUNT(1) as DataRows FROM #JSON);
		PRINT CASE WHEN @RowCount > 0 THEN 'Data Retrieved' ELSE 'Data retrieval failed, check Response OUT property' END; -- Data Retrieval Status Message

	 	IF (@RowCount > 0)
		BEGIN
	    	-- Process Glossary Table
			TRUNCATE TABLE #Glossary;
	
			INSERT INTO #Glossary (ID,SortAs,GlossTerm,Acronym,Abbrev)
 			SELECT	ID,
					SortAs,
					GlossTerm,
					Acronym,
					Abbrev
			FROM OPENJSON ( ( SELECT YourJSON FROM #JSON ) )
			WITH (
    			glossary nvarchar(max) AS JSON
			) as Glossary
			CROSS APPLY OPENJSON (Glossary)
			WITH (
    			title varchar(100),
    			GlossDiv nvarchar(max) AS JSON
			) as GlossDiv
			CROSS APPLY OPENJSON (GlossDiv)
			WITH (
				title varchar(100),
				GlossList nvarchar(max) AS JSON
			) as GlossList
			CROSS APPLY OPENJSON (GlossList)
			WITH (
				GlossEntry nvarchar(max) AS JSON
			) as GlossEntry
			CROSS APPLY OPENJSON (GlossEntry)
			WITH (
				ID varchar(20),
				SortAs varchar(20),
				GlossTerm varchar(200),
				Acronym varchar(20),
				Abbrev varchar(50)
			)
			;

			-- Refresh and Load Temp Tables into data tables
			IF (SELECT MAX([ID]) FROM #Glossary) IS NOT NULL
			BEGIN
	 			TRUNCATE TABLE dbo.glossary;
				INSERT INTO dbo.glossary
				SELECT * FROM #Glossary;

	 			-- Log Successful Process Run
				INSERT INTO dbo.process_log (RunDateTime,Process,DisplayName,Schedule,AffectedTables,AfftecedViews) VALUES (GETDATE(),'dbo.ImportListJSON','Glossary','Weekly','dbo.glossary','dbo.vGlossary');
			END

			-- Fix Data
			UPDATE dbo.glossary SET [GlossTerm]=NULL WHERE [GlossTerm]='';

		END

	END

	-- Drop Temp Tables
	DROP TABLE #Glossary;
	DROP TABLE #JSON;

 	-- Close Connection
	EXEC @Result = sp_OADestroy @Obj;
	
END

XML Format

Stored Procedure - ImportListXML

dbo.ImportListXML

CREATE PROCEDURE [dbo].[ImportListXML]
AS
BEGIN

	DECLARE @URL VARCHAR(8000);
	DECLARE @Response VARCHAR(MAX);
	DECLARE @XML xml;
	DECLARE @Obj int;
	DECLARE @Result int;
	DECLARE @HTTPStatus int;
	DECLARE @ErrorMessage varchar(8000);
	DECLARE @RowCount int;
	SET TEXTSIZE -1

	SET @URL = '<URL>';

	-- Create Temp Tables
	IF OBJECT_ID('tempdb..#Glossary', 'U') IS NOT NULL DROP TABLE #Glossary;
	CREATE TABLE #Glossary (
		ID varchar(20),
		SortAs varchar(20),
		GlossTerm varchar(200),
		Acronym varchar(20),
		Abbrev varchar(50)
    );

	IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml;
	CREATE TABLE #xml ( yourXML XML );

	-- Process and import XML file into temp table
	TRUNCATE TABLE #xml;
	
	EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Obj OUT;	-- Various sp_OACreate strings that may work if the current setting doesn't: 'MSXML2.ServerXMLHTTP' , 'MSXML2.XMLHTTP' 
	-- EXEC @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'proxy.company.com:8080'; -- Proxy Server
	EXEC @Result = sp_OASetProperty @Obj, 'setTimeouts','120000','120000','120000','120000';
	
	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;

	PRINT CASE WHEN  @HTTPStatus = 200 THEN 'Connection Successful' ELSE 'Connection Failed' END; -- Display Connection Status Message

 	IF (@HTTPStatus = 200)
	BEGIN
		INSERT #xml ( yourXML )
		EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml' --, @Response OUT
		;

	 	-- Data Retrieval Check
		SET @RowCount = (SELECT COUNT(1) as DataRows FROM #XML);
		PRINT CASE WHEN @RowCount > 0 THEN 'Data Retrieved' ELSE 'Data retrieval failed, check Response OUT property' END; -- Data Retrieval Status Message

	 	IF (@RowCount > 0)
			-- Process Glossary Table
			TRUNCATE TABLE #Glossary;

			WITH xmlnamespaces(default '<URL>/plms/schema/V1.1')
			INSERT INTO #Glossary  SELECT -- y.c.query('.') as XML_Data, -- XML Data Output for Debugging
				y.c.query('ID ').value('.', 'varchar(20)') as [ID],
				y.c.query('SortAs').value('.', 'varchar(20)') as [SortAs],
				y.c.query('GlossTerm').value('.', 'varchar(200)') as [GlossTerm],
				y.c.query('Acronym').value('.', 'varchar(20)') as [Acronym],
				y.c.query('Abbrev').value('.', 'varchar(50)') as [Abbrev]
			CROSS APPLY x.yourXML.nodes('Glossary') y(c);

			-- Refresh and Load Temp Tables into data tables
			IF (SELECT MAX(ID) FROM #Glossary) IS NOT NULL
			BEGIN
	 			TRUNCATE TABLE dbo.glossary;
				INSERT INTO dbo.glossary
				SELECT * FROM #Glossary;

	 			-- Log Successful Process Run
				INSERT INTO dbo.process_log (RunDateTime,Process,DisplayName,Schedule,AffectedTables,AfftecedViews) VALUES (GETDATE(),'dbo.ImportListXML','Glossary','Weekly','dbo.glossary','dbo.vGlossary');
			END

			-- Fix Data
			UPDATE dbo.glossary SET [GlossTerm]=NULL WHERE [GlossTerm]='';

		END

	END

	-- Close Connection
	EXEC @Result = sp_OADestroy @Obj;

	-- Drop Temp Tables
 	DROP TABLE #Glossary;
	DROP TABLE #xml;

END

CSV Format

Stored Procedure - ImportListCSV

dbo.ImportListCSV

CREATE PROCEDURE [dbo].[ImportListCSV]
AS
BEGIN
	
	DECLARE @URL VARCHAR(8000);
	DECLARE @Response VARCHAR(MAX);
	DECLARE @XML xml;
	DECLARE @Obj int;
	DECLARE @Result int;
	DECLARE @HTTPStatus int;
	DECLARE @ErrorMessage varchar(8000);
	DECLARE @SQL varchar(max);
	DECLARE @RowCount int;
	SET TEXTSIZE -1

	SET @URL = '<URL>';

	-- Create Temp Tables
 	IF OBJECT_ID('tempdb..#Glossary', 'U') IS NOT NULL DROP TABLE #Glossary;
 	CREATE TABLE #Glossary (
		ID varchar(20),
		SortAs varchar(20),
		GlossTerm varchar(200),
		Acronym varchar(20),
		Abbrev varchar(50)
    );

	IF OBJECT_ID('tempdb..#CSV') IS NOT NULL DROP TABLE #CSV;
	CREATE TABLE #CSV ( yourCSV varchar(max) );

	IF OBJECT_ID('tempdb..#CSV_Values') IS NOT NULL DROP TABLE #CSV_Values;
	CREATE TABLE #CSV_Values (
		[ID] int IDENTITY(1,1) PRIMARY KEY,
		[DataGroupID] int,
		[ColumnID] int,
		[value] varchar(max),
		[input_type] varchar(50)
	);

	-- Process and import XML file into temp table
	TRUNCATE TABLE #CSV;
	
	EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Obj OUT;	-- Various sp_OACreate strings that may work if the current setting doesn't: 'MSXML2.ServerXMLHTTP' , 'MSXML2.XMLHTTP' 
	-- EXEC @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'proxy.company.com:8080'; -- Proxy Server 
	EXEC @Result = sp_OASetProperty @Obj, 'setTimeouts','120000','120000','120000','120000';
	
	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;

 	PRINT CASE WHEN  @HTTPStatus = 200 THEN 'Connection Successful' ELSE 'Connection Failed' END; -- Display Connection Status Message

 	IF (@HTTPStatus = 200)
	BEGIN

		INSERT #CSV ( yourCSV )
		EXEC @Result = sp_OAGetProperty @Obj, 'responseText' --, @Response OUT
		;

	 	-- Data Retrieval Check
		SET @RowCount = (SELECT COUNT(1) as DataRows FROM #CSV);
		PRINT CASE WHEN @RowCount > 0 THEN 'Data Retrieved' ELSE 'Data retrieval failed, check Response OUT property' END; -- Data Retrieval Status Message

	 	IF (@RowCount > 0)
		BEGIN
			UPDATE #CSV SET yourCSV=REPLACE(yourCSV,',,',',"NULL",');
			UPDATE #CSV SET yourCSV=REPLACE(yourCSV,',"",',',"NULL",');

			-- Process CSV Table
			INSERT INTO #CSV_Values ( [value] )
			SELECT [value]
			FROM STRING_SPLIT( (SELECT yourCSV FROM #CSV) , '"' )
			WHERE [value] NOT IN ('',',')
			;

			-- Determine Input Types
				-- New Lines
				UPDATE #CSV_Values SET input_type='New Line' WHERE REPLACE(REPLACE([value],CHAR(13),''),CHAR(10),'')='';
	
				-- Header Columns
				UPDATE CV
				SET CV.input_type='Header'
				FROM #CSV_Values CV
				INNER JOIN (
					SELECT MIN(ID) as MinID
					FROM #CSV_Values
					WHERE input_type='New Line'
				) CVD ON CV.ID<CVD.MinID
				;

				-- Suffix Information
				UPDATE CV
				SET input_type='Suffix'
				FROM #CSV_Values CV
				INNER JOIN #CSV_Values CVD ON CV.ID>=CVD.ID AND CVD.[value] LIKE '%Generated %';

				-- Data
				UPDATE #CSV_Values SET input_type='Data' WHERE input_type IS NULL;

			-- Data Group ID
				-- Header
				UPDATE CV
				SET CV.DataGroupID=0
				FROM #CSV_Values CV
				CROSS JOIN (SELECT MAX(ID)+1 as HeaderNewLineID FROM #CSV_Values WHERE input_type='Header') HNL
				WHERE CV.input_type='Header'
					OR CV.ID=HNL.HeaderNewLineID
				;

				-- Data
				DECLARE @NewLineID int = (SELECT MIN(ID) as ID FROM #CSV_Values WHERE input_type='New Line' AND DataGroupID IS NULL);
				DECLARE @MaxID int = (SELECT MAX(ID) as ID FROM #CSV_Values WHERE input_type='Data');
				DECLARE @GroupID int = 1;

				WHILE ( @NewLineID <= @MaxID )
				BEGIN
					UPDATE CV
					SET CV.DataGroupID=@GroupID
					FROM #CSV_Values CV
					WHERE CV.DataGroupID IS NULL
						AND CV.ID<@NewLineID
						AND CV.input_type='Data';

					UPDATE #CSV_Values SET DataGroupID=@GroupID WHERE ID=@NewLineID;

					SET @GroupID = @GroupID + 1;
					SET @NewLineID = (SELECT MIN(ID) as ID FROM #CSV_Values WHERE input_type='New Line' AND DataGroupID IS NULL)

				END

				-- Last Row of Data / No New Line Fix
				UPDATE CV
				SET CV.DataGroupID=@GroupID
				FROM #CSV_Values CV
				WHERE CV.DataGroupID IS NULL
					AND CV.input_type='Data';

			-- Determine Column IDs for Header and Data
			UPDATE CV
			SET CV.ColumnID=CVD.ColumnID
			FROM #CSV_Values CV
			INNER JOIN (
				SELECT	ID,
						ROW_NUMBER() OVER (PARTITION BY [DataGroupID] ORDER BY [ID]) as ColumnID
				FROM #CSV_Values
			) CVD ON CV.ID=CVD.ID
			WHERE input_type IN ('Header','Data')
			;

			-- Determine Header IDs and Names for Pivot
			DECLARE @Columns VARCHAR(MAX) = '';
			SELECT @Columns += QUOTENAME([ColumnID]) + ','
			FROM #CSV_Values
			WHERE input_type='Header'
			ORDER BY ColumnID
			;
			SET @columns = LEFT(@columns, LEN(@columns) - 1);

			DECLARE @ColumnRef VARCHAR(MAX) = '';
			SELECT @ColumnRef += QUOTENAME([ColumnID]) + ' AS '+QUOTENAME([value])+', '
			FROM #CSV_Values
			WHERE input_type='Header'
			ORDER BY ColumnID
			;
			SET @ColumnRef = LEFT(@ColumnRef, LEN(@ColumnRef) - 1);

			-- Pivot and Insert into Temp Table
			TRUNCATE TABLE #Glossary;
			SET @SQL = '
				INSERT INTO #Glossary   SELECT	'+@ColumnRef+'
				FROM (
					SELECT DataGroupID, ColumnID, [value]
					FROM #CSV_Values
				) CSV
				PIVOT 
				(   MAX([value])
					FOR [ColumnID]
					IN ('+@Columns+')
				) AS PivotTable
				WHERE DataGroupID>0
				ORDER BY DataGroupID
			';
			EXEC (@SQL);

			-- Refresh and Load Temp Tables into data tables
			IF (SELECT MAX(ID) FROM #Glossary) IS NOT NULL
			BEGIN
				TRUNCATE TABLE dbo.glossary;
				INSERT INTO dbo.glossary
				SELECT * FROM #Glossary;

				-- Log Successful Process Run
				INSERT INTO dbo.process_log (RunDateTime,Process,DisplayName,Schedule,AffectedTables,AfftecedViews) VALUES (GETDATE(),'dbo.ImportListCSV','Glossary','Daily','dbo.glossary','dbo.vGlossary');
			END

 			-- Fix Data
			UPDATE dbo.glossary SET [GlossTerm]=NULL WHERE [GlossTerm]='';

		END

	END

	-- Close Connection
	EXEC @Result = sp_OADestroy @Obj;

     -- Drop Temp Tables
	DROP TABLE #Glossary;
	DROP TABLE #CSV;
	DROP TABLE #CSV_Values;

END

Barcodes

For this example, we used the online open source barcode generator available from Metafloor.

Table - Barcodes

dbo.Barcodes

CREATE TABLE [dbo].[Barcodes](
	[ProductID] [varchar](18) NOT NULL,
	[Barcode] [varbinary](max) NULL,
	[CreatedDateTime] [datetime] NULL,
 CONSTRAINT [PK_Barcodes] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)
GO

ALTER TABLE [dbo].[Barcodes] ADD  CONSTRAINT [DF_Barcodes_CreatedDateTime]  DEFAULT (getdate()) FOR [CreatedDateTime]
GO

Function - fnBase64ToBinary

dbo.fnBase64ToBinary

CREATE FUNCTION [dbo].[fnBase64ToBinary]
(
    @Str AS NVARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
AS
BEGIN
    RETURN (
        SELECT
                CONVERT(
                    VARBINARY(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
                )
        FROM    (SELECT @Str AS BASE64_COLUMN) A
    );
END;
GO

Function - fnBinaryToBase64

dbo.fnBinarytoBase64

CREATE FUNCTION [dbo].[fnBinaryToBase64]
(
    @Var AS VARBINARY(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT  @Var AS '*' FOR XML PATH('')
    );
END;
GO

View - vBarcodes

This is a view that is Power BI ready. When importing into Power BI display the Barcode column as an "Image URL".

dbo.vBarcodes

CREATE VIEW [dbo].[vBarcodes] as
SELECT MB.ProductID as [Product ID]
      ,CONCAT('data:image/png;base64,',dbo.fnBinaryToBase64(MB.[Barcode])) as [Barcode]
	  ,CAST(MB.CreatedDateTime as date) as [Barcode Generated Date]
	  ,CAST(MB.CreatedDateTime as time) as [Barcode Generated Time]
  FROM dbo.Barcodes MB

Stored Procedure - GenerateBarcodes

dbo.GenerateBarcodes

CREATE PROCEDURE [dbo].[GenerateBarcodes]
AS
BEGIN

	-- Generate Barcode Data
	DECLARE @CurrentIndex int = 1;
	DECLARE @MaxIndex int;
	DECLARE @RandomTimeQuery varchar(25);
	DECLARE @Item varchar(20);
	DECLARE @BarcodeInput varchar(20);
	DECLARE @URL varchar(4000);
	DECLARE @Response VARCHAR(8000);
	DECLARE @XML xml;
	DECLARE @Obj int;
	DECLARE @Result int;
	DECLARE @HTTPStatus int;
	DECLARE @ErrorMessage varchar(max);
	DECLARE @RowCount int;
	SET TEXTSIZE 32768;

	-- Create Temp Tables
	IF OBJECT_ID('tempdb..#Product') IS NOT NULL DROP TABLE #Product;
	CREATE TABLE #Product ( 
		ProductID varchar(20),
		BarcodeInput varchar(20),
		ProcessIndex int 
	);

	IF OBJECT_ID('tempdb..#Barcode') IS NOT NULL DROP TABLE #Barcode;
	CREATE TABLE #Barcode ( 
		ProductID varchar(20),
		Barcode varbinary(max)
	);

	-- Determine Barcodes to process
	INSERT INTO #Product (ProductID,BarcodeInput,ProcessIndex)
	SELECT	MD.[ProductID] as ProductID,
			MD.[ProductID] as BarcodeInput,
			ROW_NUMBER() OVER (ORDER BY MD.[ProductID]) as ProcesIndex
	FROM dbo.vProducts MD
	LEFT OUTER JOIN dbo.Barcodes MB ON MD.[ProductID]=MB.ProductID
	WHERE MB.Barcode IS NULL
	ORDER BY MD.[ProductID]
	;

	-- Process Item Numbers for Barcodes
	SET @MaxIndex = (SELECT MAX(ProcessIndex) FROM #Glossary);

	IF @MaxIndex > 0
	BEGIN
		WHILE ( @CurrentIndex <= @MaxIndex )
		BEGIN
			SET @BarcodeInput = (SELECT BarcodeInput FROM #Product WHERE ProcessIndex=@CurrentIndex);
			SET @ProductID = (SELECT ProductID FROM #Product WHERE ProcessIndex=@CurrentIndex);
			SET @URL = 'http://bwipjs-api.metafloor.com/?bcid=code128&text=' + @BarcodeInput + '&includetext'; -- External API URL (Metafloor)
			SET @HTTPStatus = 0;

			TRUNCATE TABLE #Barcode;
			
			EXEC @Result = sp_OACreate 'Msxml2.ServerXMLHTTP.6.0', @Obj OUT;	-- Various sp_OACreate strings that may work if the current setting doesn't: 'MSXML2.ServerXMLHTTP' , 'MSXML2.XMLHTTP' 
		    -- EXEC @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'proxy.company.com:8080'; -- Proxy Server
			EXEC @Result = sp_OASetProperty @Obj, 'setTimeouts','120000','120000','120000','120000';

			EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false;
			EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'image/jpeg';	
			EXEC @Result = sp_OAMethod @Obj, send, NULL,'';
			EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT;

		 	PRINT CASE WHEN  @HTTPStatus = 200 THEN @BarcodeInput + ' Connection Successful' ELSE @BarcodeInput + 'Connection Failed' END; -- Display Connection Status Message

 			IF (@HTTPStatus = 200)
			BEGIN
				INSERT #Barcode ( Barcode )
				EXEC @Result = sp_OAGetProperty @Obj, 'responseBody'--, @Response OUT
				;
				UPDATE #Barcode SET ProductID=@ProductID WHERE Item IS NULL;

				-- Data Retrieval Check
				SET @RowCount = (SELECT COUNT(1) as DataRows FROM #Barcode WHERE Barcode IS NOT NULL);
				PRINT CASE WHEN @RowCount > 0 THEN @BarcodeInput + ' Data Retrieved' ELSE @BarcodeInput + ' Data retrieval failed, check Response OUT property' END; -- Data Retrieval Status Message

				IF (@RowCount > 0)
				BEGIN
					INSERT INTO dbo.Barcodes (ProductID,Barcode)
					SELECT	ProductID,
							Barcode
					FROM #Barcode
					;
				END

				-- Increment Process Index
				SET @CurrentIndex = @CurrentIndex + 1;
				SET @RandomTimeQuery = '00:00:02';
				WAITFOR DELAY @RandomTimeQuery;
			END

		 	-- Close Connection
			EXEC @Result = sp_OADestroy @Obj;

		END
	END

	-- Data Fixes
	DELETE FROM [dbo].[Barcodes] WHERE dbo.fnBinaryToBase64(Barcode) NOT LIKE 'iVBOR%' -- Delete rows that are not barcode images
	DELETE FROM [dbo].[Barcodes] WHERE DATALENGTH([Barcode])<2000 -- Delete incomplete file transfers

		-- Delete Barcodes for deleted products
		DELETE MB
		FROM dbo.Barcodes MB
		LEFT OUTER JOIN dbo.vProducts MD ON MB.ProductID=MD.ProductID
		WHERE MD.ProductID IS NULL
		;

	-- Drop Temp Tables
	DROP TABLE #Product;
	DROP TABLE #Barcode;

END

Source

https://stackoverflow.com/questions/66406972/sql-server-web-xml-to-table

https://stackoverflow.com/questions/45664937/retrieve-varbinary-value-as-base64-in-mssql

https://www.zealousweb.com/calling-rest-api-from-sql-server-stored-procedure/

Page tree

Stored Procedures and Functions for Web Based APIs