Please find below an example of the code that will enable a stored procedure to draw upon XML or external data sources.
USE master; GO sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO |
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]; |
Please refer to the article about Scheduled Stored Procedures and Log Setup
{ "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.
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 |
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 |
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 |
For this example, we used the online open source barcode generator available from Metafloor.
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 |
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 |
CREATE FUNCTION [dbo].[fnBinaryToBase64]
(
@Var AS VARBINARY(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT @Var AS '*' FOR XML PATH('')
);
END;
GO |
This is a view that is Power BI ready. When importing into Power BI display the Barcode column as an "Image URL".
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 |
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 |
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/