Is it possible to bind HTML templates without using a loop in sql

Question

Is it possible to bind HTML templates with JSON data dynamically without using a loop in sql server?

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

DECLARE @htmlTemplate NVARCHAR(MAX)
SET @htmlTemplate = N'

  <h1>Name: ' + (SELECT JSON_VALUE(@json, '$[0].name')) + '</h1>
  <p>Age: ' + (SELECT JSON_VALUE(@json, '$[0].age')) + '</p>
'

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

Amit Mohanty · Answer

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

Naimish Makwana · Answer

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 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