Post
Ask Question

Dynamically Bind HTML Template to JSON Data Without using Loop in SQL

Introduction

This SQL code creates a stored procedure called InsertHTMLTemplates that accepts a single @json input argument with the NVARCHAR(MAX) data type. This stored procedure's purpose is to use the JSON data as input to add HTML templates to a table called templatedemo.

How can we dynamically bind HTML templates to JSON data without using a looping SQL Server?

CREATE PROCEDURE InsertHTMLTemplates
    @json NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO templatedemo (template)
    SELECT 
        '<h1 style="color: red; font-style: italic; font-size: 12px;">' + JSON_VALUE(value, '$.name') + '</h1>' +
        '<p>Age: ' + JSON_VALUE(value, '$.age') + '</p>'
    FROM OPENJSON(@json);
END;
DECLARE @json NVARCHAR(MAX) = '[{"name": "Ishika mishra", "age": 25}, {"name": "Shubham Mishra", "age": 29}, {"name": "Sweety tiwari", "age": 25}]';

EXEC InsertHTMLTemplates @json;

Output

select *from templatedemo

Output

Create PROCEDURE InsertHTMLTemplates: This line specifies the creation of the InsertHTMLTemplates stored procedure in the SQL Server database.

CREATE PROCEDURE InsertHTMLTemplates

NVARCHAR(MAX) for @json: A parameter for the stored process is declared in this line. It assumes a JSON-formatted input string that will be utilized to create HTML templates.

@json NVARCHAR(MAX)

SELECT: This subquery creates the HTML templates using the supplied JSON data.

Element in HTML is created in this section with the name taken directly from the JSON data and formatted specifically. The value connected to the 'name' key in the JSON data is extracted using the JSON_VALUE function.

SELECT '<h1 style="color: red; font-style: italic; font-size: 12px;">' + JSON_VALUE(value, '$.name') + '</h1>' +'<p>Age: ' + JSON_VALUE(value, '$.age') + '</p>'

@json parameter is used to pass JSON data to the FROM OPENJSON(@json) line, which uses the OPENJSON function to parse the input. You can work with JSON data in SQL Server thanks to the OPENJSON function. It provides a result that resembles a table with columns that correspond to the JSON data's structure, including a column called "value" in this example that holds the JSON values.

 FROM OPENJSON(@json);

Conclusion

This stored procedure inserts HTML templates into the templatedemo table using JSON data as its input. It creates these templates by removing particular values from the JSON and putting them into HTML elements. For the 'name' field, it generates an HTML< h1> element in red with an italic font style and a 12px font size, and for the 'age' field, it generates an <p> element. The 'template' column of the templatedemo table then holds the created HTML templates.

FAQs

Q1. What is the purpose of the InsertHTMLTemplates stored procedure?

Ans. The purpose of this stored procedure is to insert HTML templates into a database table based on JSON data provided as input.

Q2. What does the @json parameter in the stored procedure do?

Ans. The @json parameter is used to pass a JSON string as input to the stored procedure. This JSON string is then parsed and used to generate HTML templates.

Q3. How does the OPENJSON function work in this code?

Ans. The OPENJSON function is used to parse the JSON data provided in the @json parameter and convert it into a tabular format, making it easier to work with in SQL.

Q4. What are the HTML templates generated by the code?

Ans. The code generates HTML templates that include an <h1> element to display a name in red, italicized, and with a font size of 12px, as well as a <p> element to display an age.

Q5. How are values from the JSON data integrated into the HTML templates?

Ans. The JSON_VALUE function is used to extract values from the JSON data and incorporate them into the HTML templates.

Q6. Can this stored procedure handle JSON data with different structures?

Ans. Yes, the code is designed to work with JSON data, and it extracts values based on the keys provided in the JSON, so it can handle JSON with various structures.

Q7. Is there a limit to the size of the JSON data that can be passed to the stored procedure?

Ans. The @json parameter is declared as NVARCHAR(MAX), so it can handle JSON data of considerable size.


Similar Articles