Creating html content with SQL

Question

I am using Microsoft SQL Server 2016. I can use it in another version.

I have variables of an html page content in my tables.

SQL Server tables:

PageVariables
Menus
Slides etc.

Can I generate page html in SQL Server without programming?

I tried to do it with Replace partially. But it doesn't work on multi-line tables. I don't think this is the right way either.

Is there something like SQL Server in javascript like mustache, vue, javascript literal?

declare @template nvarchar(max)='<html><head>{title}</title></head><body><div class="menu">{menu}</div></body></html>'

declare @title  nvarchar(max)='My Page'
declare @menu  nvarchar(max)=''

SELECT 'My Menu-1' AS 'MenuName',
       '?link=1' AS 'MenuLink'
UNION ALL
SELECT 'My Menu-2' AS 'MenuName',
       '?link=2' AS 'MenuLink'

/*
for @menu
    set @menu='<a href="@MenuLink">@MenuName</a>'
next
*/
SET @template = REPLACE(@template, '{title}', @title)
SET @template = REPLACE(@template, '{menu}', @menu)

SELECT @template

it is also difficult in this way.

        SELECT '?link=1' AS '@href',
               'My Menu-1' AS 'span'

        UNION ALL
        SELECT '?link=2' AS '@href',
               'My Menu-2' AS 'span'

        FOR XML PATH ('a'), ROOT ('div')

As a general rule, SQL is not meant to produce user interfaces. It's meant for storing and retrieving data to/from a database. You can perform some level of rendering, but you'll soon find it's not meant for it. You'll need an app layer, albeit simple. — The Impaler, Jul 10 '20 at 19:48

John Cappelletti · Answer 1 · 2020-07-10T20:41:43.277

It is doable, but The Impaler is correct, you may outgrow this technique quickly.

The map table can have as many items as needed. Even items not needed, they will be ignored.

Here is a simplified example.

declare @template nvarchar(max)='
<html>
    <head>
        <title>{title}</title>
    </head>
    <body>
        <div class="welcome">{username}</div>
        <div class="menu">
            {menu}
        </div>
    </body>
</html>
'

Declare @map table (Seq int,Item varchar(100),Value varchar(max))
Insert Into @map values
 (1   ,'{title}','My Page')
,(2   ,'{username}','Mary-Jane')
,(9999,'{title}','')             -- clean-up
,(9999,'{username}','')          -- clean-up
,(9999,'{menu}','')              -- clean-up


-- Generate from select ... note the trailing {menu}
Insert Into @map values  
 (1  ,'{menu}','<a href="My Menu-1">?link=1</a>{menu}')
,(2  ,'{menu}','<a href="My Menu-2">?link=2</a>{menu}')



Select @template=replace(@template,Item,Value)
 From  @map
 Order By Seq

Print @template

Returns

<html>
    <head>
        <title>My Page</title>
    </head>
    <body>
        <div class="welcome">Mary-Jane</div>
        <div class="menu">
            <a href="My Menu-1">?link=1</a><a href="My Menu-2">?link=2</a>
        </div>
    </body>
</html>

Very nice and shows how he could build the tables to make his web site data-driven rather than generating the HTML in SQL. Also, beats trying to COALESCE, etc all the rows and strings together. — Pete -S-, Jul 10 '20 at 20:33
@Pete-S- Very kind. You're right, I use tables, but also some fun items which dynamically unpivots a record (not dynamic sql), for items like FName,LName,EMail, Date Strings, etc. — John Cappelletti, Jul 10 '20 at 20:37

score 0 · Accepted Answer · answered Jul 10 '20 at 20:08

0

What you are looking to do is one of these string concatenation operations when you have multiple rows that you need to combine into single entry:

Concatenate Multiple Rows Using FOR XML PATH
Concatenate Rows Using COALESCE
Using STRING_AGG

answered Jul 10 '20 at 20:08

Pete -S-

542
5
13

Creating html content with SQL

2 Answers2