Creating html content with SQL
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 @templateit 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')
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> 
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
