manage mailing with XML templates

  • Hello everyone, I'm a newbie and didn't knew where exactly to post this thread. I finally decided to post on XML forum as I believe it is related with it. Here is my question(s):

    I wanted to create a procedure that will receive a string and ouput another string, more or less like this:

    Input parameter: "Dear {client_id} your order {order_Id} with the enclosed {products_Id} has been shipped"

    Result: "Dear 'Yaroslav' your order 'Books on SQL Server' with the enclosed 'XML on Sql, SQL Tuning, SQL administration' has been shipped."

    I began the analysis and thought on 2 tables, one with the tokens and another one with templates of the emails or messages.

    Tables would be:

    CREATE TABLE [dbo].[Token](

    [TokenPk] [int] NOT NULL,

    [Name] [nvarchar](50) NOT NULL, --this is the string to be used on the email: {client_id} for example

    [Value] [nvarchar](50) NOT NULL, --this value will be the one used, but on my case, is a table.column reference: for {client_id} the value could be {clients.client_name}

    [Description] [nvarchar](max) NULL, --generic description of the token

    CONSTRAINT [PK_Token] PRIMARY KEY CLUSTERED ([TokenPk] ASC) ON [PRIMARY]

    CREATE TABLE [Clients].[DefaultMessage](

    [DefaultAnswerPk] [int] IDENTITY(1,1) NOT NULL,

    [Text] [nvarchar](max) NULL, --text of the message

    [PriorityOrder] [int] NOT NULL, --there could be several messages available for same question, so we need a priority order

    [DefaultMessageCategoryFk] [int] NOT NULL, --points to another table where we have several message categories

    CONSTRAINT [PK_DefaultAnswer_1] PRIMARY KEY CLUSTERED ([DefaultAnswerPk] ASC) ON [PRIMARY]

    After giving second thoughts I believed XML/XSD/XSL could be handy here. The templates instead of plain text could be XML templates. Also I could have XSL files attached so we could create different formats for same email. And finally use XSD to validate the data. Even could use XSLT to transform the data and output pdf, html, doc, etc. Then I began to read anything related with XML on SQL Server as I want to process as much as I can using TSQL sentences. I've read all the XML workshop series by Jacob Sebastian and done several testings, more still to do.

    My questions are:

    - Is this "achiveable" on TSQL?

    - Has someone done something alike? Does not matter if it was using plain text, maybe it can help bringing some light to the matter

    - Wich should be my first steps towards creating a first small working procedure/function using XML to solve this?

    More questions will arise for sure, but now I'm somewhat lost on where to continue...

  • Corrected the script and added new columns to the tables related to the XML/XSL templates:

    CREATE TABLE [DefaultMessage](

    [DefaultMessagePk] [int] IDENTITY(1,1) NOT NULL,

    [Text] [nvarchar](max) NULL,

    [PriorityOrder] [int] NOT NULL,

    [DefaultMessageCategoryFk] [int] NOT NULL,

    [XMLTemplate] [nvarchar](max) NULL, --self descriptive, will hold the XML template for this message

    [XSLTemplate] [nvarchar](max) NULL, --self descriptive, will hold the XSL template for this message

    CONSTRAINT [PK_DefaultAnswer_1] PRIMARY KEY CLUSTERED ([DefaultMessagePk] ASC)) ON [PRIMARY]

    GO

    ALTER TABLE [DefaultMessage] ADD CONSTRAINT [DF_DefaultAnswer_Order] DEFAULT ((0)) FOR [PriorityOrder]

    GO

    CREATE TABLE [DefaultMessageToken](

    [DefaultAnswerTokenPk] [int] NOT NULL,

    [Name] [nvarchar](50) NOT NULL,

    [Description] [nvarchar](max) NULL,

    [ValueWhenNull] [nchar](10) NOT NULL,

    [QueryToExecute] [nvarchar](500) NULL, --query to be executed to get the value of the current token

    CONSTRAINT [PK_DefaultAnswerToken] PRIMARY KEY CLUSTERED ([DefaultAnswerTokenPk] ASC)) ON [PRIMARY]

    GO

    ALTER TABLE [DefaultMessageToken] ADD CONSTRAINT [DF_DefaultAnswerToken_ValueWhenNull] DEFAULT ('') FOR [ValueWhenNull]

    GO

    INSERT INTO [DefaultMessage](Text, PriorityOrder, DefaultMessageCategoryFk, XMLTemplate, XSLTemplate)

    VALUES ('Hola {Client}, no hemos recibido su pago.',0,3,NULL,NULL),

    ('Hola {Client}, hemos recibido un importe diferente al esperado.',0,4,NULL,NULL),

    ('Testeando respuestas predefinidas para cuando un cliente hace una pregunta.',1,4,NULL,NULL),

    ('Mas pruebas de FAQ',1,3,NULL,NULL)

    INSERT INTO [DefaultMessageToken](DefaultAnswerTokenPk,Name, Description, ValueWhenNull, QueryToExecute)

    VALUES (1,'{Client}','Customer name','Customer','SELECT CustomerName FROM Customer WHERE Customer_Id=@Customer_Id')

    Now on the DefaultMessage table I will have the XML/XSL templates for each message.

    On the DefaultMessageToken will have each token (=tag) with the query to be executed to get the value

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply