Converting JSON Key-Value Pair Dataset into HTML

-- Convert JSON Key-Value Pair Dataset into HTML --

-- Function: fnJsonToUlList
-- Author: KrishanG
-- Date: 13/06/2023
-- Description: Converts a JSON key-value pair dataset into an HTML unordered list (UL)

-- Example usage of the @json parameter:
-- SET @json = '{"Title":"Mrs","FirstName":"Grace","LastName":"Gibbs","SurnameAtSchool":"Miles","email":"gibbs@outlook.com","PhoneNumber":"0402 935 000","YearLeft":"2013","ContactYesNo":"No","ProfTopic":"N\/A","MoreDetails":"N\/A"}'

-- ================================================
-- Function Definition
-- ================================================
ALTER FUNCTION fnJsonToUlList 
(
    -- Parameters
    @json nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    -- Variable declaration
    DECLARE @Result nvarchar(max)

    -- Conversion logic
    SELECT @Result = REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(@json, '{"', '<ul><li><div><strong>'),
                        '":"', '</strong> : '
                    ),
                    '","', '</div> </li><li><div><strong>'
                ),
                '"}', '</div></li> </ul>'
            )

    -- Return the result
    RETURN @Result
END
GO

Have you ever encountered a dataset with a JSON field containing a variable set of key-value pairs? If you've faced the challenge of rendering this data in HTML for use in an SSRS report, I ended up creating this. A SQL Server function that converts a JSON key-value pair dataset into an HTML unordered list (UL).

This function takes a JSON string as input and generates the corresponding HTML UL structure. The resulting HTML can be seamlessly integrated into your SSRS reports, providing a visually appealing way to display JSON data.

To use the function, simply provide your JSON dataset as an argument. The function will convert each key-value pair into an HTML list item (LI) element with embedded strong tags to emphasize the keys. Nested JSON structures are also supported, ensuring a comprehensive transformation of your data.