You can use OPENJSON to parse the JSON array, and then we construct the HTML template for each item within the SELECT statement.:

DECLARE @json NVARCHAR(MAX) = '[{"name": "John Doe", "age": 30}, {"name": "Jane Smith", "age": 25}, {"name": "Bob Johnson", "age": 35}]'

CREATE TABLE templatedemo (template NVARCHAR(MAX))

INSERT INTO templatedemo (template)
SELECT '
  
Name: ' + JSON_VALUE(value, '$.name') + '
  
Age: ' + JSON_VALUE(value, '$.age') + '
' 
FROM OPENJSON(@json);

SELECT * FROM templatedemo

In SQL Server, it is not possible to directly bind HTML templates with JSON data dynamically without using a loop or some form of dynamic SQL or custom code to iterate over the JSON data and generate HTML content for each item. SQL Server does not provide a built-in mechanism to perform this kind of transformation.

You would typically need to use a programming language or scripting language (e.g., C#, Python, JavaScript) to process the JSON data and generate the HTML content dynamically, using a loop or similar construct to iterate over the JSON elements. Once you have generated the HTML content for each item, you can then insert it into the database.

Here's an example of how you might do this using a SQL Server Stored Procedure and a loop in T-SQL:

-- Sample table to store HTML templates
CREATE TABLE templatedemo (template NVARCHAR(MAX))

-- Sample JSON data
DECLARE @json NVARCHAR(MAX) = '[{"name": "John Doe", "age": 30}, {"name": "Jane Smith", "age": 25}, {"name": "Bob Johnson", "age": 35}]'

-- Sample HTML template
DECLARE @htmlTemplate NVARCHAR(MAX)

-- Loop through the JSON data and generate HTML for each item
DECLARE @index INT = 0
WHILE @index < JSON_QUERY(@json, '$') IS NOT NULL
BEGIN
    SET @htmlTemplate = N'
      
Name: ' + JSON_VALUE(@json, '$[' + CAST(@index AS NVARCHAR(10)) + '].name') + '
      
Age: ' + JSON_VALUE(@json, '$[' + CAST(@index AS NVARCHAR(10)) + '].age') + '
'

    -- Insert the HTML template into the 'templatedemo' table
    INSERT INTO templatedemo (template) VALUES (@htmlTemplate)

    SET @index = @index + 1
END

Thanks