September 29, 2022 at 11:07 am
Morning all, Looking for any advice or thoughts on how to achieve the impossible 🙂
I have been asked to extend a Proof of Concept to send out emails with report data. I want to keep the whole thing generic (as much as possible) so I would like to be able to pick up a template file, pick up a dataset (or sets) and inject the data into the template. I would also like to be able to include css styling in the template.
In an ideal world it would look something like Vue.js or Angualar templating.
data = [
{
"id": "one",
"someField" : "a value",
"nestedObjectArray" : [
{
"nestedid" :"one-one",
"nestedValue" : "nv-one-one"
},
{
"nestedid" :"one-two",
"nestedValue" : "nv-one-two"
},
]
},
{
"id": "two",
"someField" : "another value",
"nestedObjectArray" : [
{
"nestedid" :"two-one",
"nestedValue" : "nv-two-one"
},
{
"nestedid" :"two-two",
"nestedValue" : "nv-two-two"
},
]
}
]
template = N'
<html>
<div #for="record in data">
<h1>{{record.someField}}</h1>
<uk>
<li #for="nested in record.nestedObjectArray">
{{nested.nestedValue}}
</li>
</ul>
</div>
</html>
I would need a template parser that takes the data JSON object and when it sees a #for it repeats the html tag block for each record at the correct level and anything inside double curly braces {{...}} is replaced by the correct field from the current iteration of the array
in the above code we would end up with two h1 headings with two unordered lists of two items
a value
another value
I woudl then use some funky code to decide which datasets needed to be fetched into the data model and which template to pick up, parse the details to a file and send mail with the file as the html body.
My limitation due to security and team expertise is that I have to stay fully within MSSQL stack so T-SQL and SSIS, preferably with no CLR functions.
Has anyone done anything similar and if so could I have a few pointers.
Also some of these reports could be quite long (especially with all the html tags) so VARCHAR(max) is not going to cut it. Will likely have to BCP the results to a file.
September 29, 2022 at 1:26 pm
My experience in the past was that many email clients didn't handle CSS well, & I ended up needing to rely on tables & HTML formatting tags rather than CSS to get consistent readable emails.
Check out links like these for CSS support by client -- it looks like there are still many gaps/inconsistencies. If your target base is on the same/limited client(s), perhaps it can work for you.
September 29, 2022 at 4:37 pm
CSS support is still patchy, but a lot of that is because CSS is moving so quickly now and the parsing engine does not exist when viewing on older email platforms.
CSS would likely be pretty basic: background colours, font weight, width in px etc rather than using grid or flexbox.
It may be that I have to inline the CSS but again that could be built into a parser. but it does make the HTML file extremely verbose.
CSS is a nice-to-have. the harder part is injecting the iterated data. There may be multiple collections in the data and each object in the collection could be several levels of nesting. Think
This is the bit which is really cranking my cranium at the moment.,,,
I have managed to build a basic template parser. I will share the solution when I have a working prototype.
September 29, 2022 at 5:44 pm
https://www.sqlservercentral.com/forums/topic/create-html-email#post-3889947
https://www.sqlservercentral.com/forums/topic/create-total-record-in-html-email
Review both of these - from these you should be able to put together a template that works for your requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply