Proposal Automation Software

Database Mail Merge from Traditional Databases

Introduction


In every organization, the database plays an important role in holding the entire organization’s data. Traditional databases such as IBM, Microsoft & Oracle control close to 80% of DBMS market with Oracle leading the pack. Oracle, MySQL, and SQL server occupy the top 3 positions in latest DB engine rankings.


In traditional databases, data inside the database is stored in tables and they have a logical connection among them. Hence, Database solutions don’t have document generation capabilities.


For organizations that generate loads of documents, manual creation of documents from database records is a costly and time-consuming task.

Start Database Mail Merge






Database mail merge with EDocGen


The document templates undergo frequent changes. Hence it’s a good idea for business users to have a DIY database mail merge system. That reduces their dependency on IT department and gives the flexibility to change templates as-and-when required.


EDocGen is the best solution for business users to generate documents in bulk and on-demand from traditional databases. Utilizing existing Word and PDF templates, they can perform database mail merge to create PDF and Microsoft Word documents.


  1. Bulk document creation - Produce high volumes of documents on a scheduled basis or interactively
  2. On-Demand document creation - Real-time demand document creation as part of work-flow. Ex: insertion/update/deletion of a record in database triggers document generation.

In this article, we limit ourselves to Oracle, MySQL, and SQL server databases as they are the top 3 traditional database engines. You can extrapolate this to other traditional relational databases.


Database mail merge: Bulk Document Generation


Oracle Database mail merge


Using EDocGen database mail merge, generate documents from Oracle database in two steps.

  1. Execute SQL to retrieve data in XML/JSON format. Database administrators can create a cron job to automate this.
  2. Populate this XML/JSON into templates

Step1: Exporting XML and JSON from Oracle DB

SQL allows you to retrieve Oracle DB data in XML format. Writing SQL is a one time job and SQL can be changed easily whenever template changes. System admins (DBA) and many business users are adept at SQL. Following example explain how to export Oracle database data as XML using XMLAgg and XMLElement.

SELECT XMLAgg(XMLElement(“Department”,XMLAttributes(department_id as “ID”, department_name))
FROM departments 

Since Oracle Database release 12.2 Oracle also allow the creation of JSON data using 4 new SQL operators JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG. A combination of these operators can create a complex JSON instance.


SELECT JSON_ARRAY(first, last) FROM customers;

Armed with XML/JSON, the next step is to populate this XML into the document template.

Step2: Populating XML/JSON into Document Template

EDocGen database mail merge supports filling of dynamic text, tables, and images from data into document templates. Please visit XML to Word and JSON to Word blog for a detailed description on how to go about XML and JSON population into Word templates. Briefly,

1. Add relevant tags to dynamic fields in the template and upload them to EDocGen

2. Populate it with XML by uploading XML and click generate

3. Download generated documents


Apart from docx documents, you can also generate PDF documents. Please visit XML to PDF for details.

MySQL Database mail merge


MySql has no built-in support for creating XML in its SQL implementation. So we take a different approach. As you know EDocGen supports the population of both JSON and Excel files.

MySQL allows you to export SQL data into a local CSV file. The SQL syntax for exporting data into local CSV file is as follows.


SELECT department_id, department_name
FROM departments
INTO OUTFILE '/path/to/file.csv
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
FIELDS ESCAPED BY '\'
LINES TERMINATED BY '\n';

You can also export query data into JSON file as well as shown below using ‘JSON_OBJECT’ function.


SELECT CONCAT('[',GROUP_CONCAT(JSON_OBJECT('ID', department_id, 'name', department_name)),']')
FROM departments 

The next step is to populate CSV/JSON file into template similar to the way we did with Oracle.


Mail merge from SQL Server Database


Like Oracle, MSSQL also allows you to retrieve data in XML format. Use XML RAW function to generate XML and JSON from MSSQL queries, as shown below.


SELECT department_id, department_name				
FROM departments			
FOR XML RAW (departments), ROOT (departments), ELEMENTS;

SELECT name, surname
FROM emp
FOR JSON AUTO 
 The results in JSON.

        [{
        "name": "John"
        }, {
        "name": "Jane",
        "surname": "Doe"
        }] 

The next step is to populate this XML file into template similar to the way we did with Oracle. Refer to generate word documents from SQL server and generate PDF documents from SQL server for a detailed account.


Database mail merge: On-Demand Document Generation


With on-demand document generation, users will be able to request the generation of documents and instantly view it on their browsers or generate and use them as part of the workflow. An example workflow is - your web visitor fills a form to receive a proposal document by mail.


Oracle Database mail merge:

Oracle REST Data Services (ORDS) is a free tool that enables users to turn their Oracle databases into RESTful API Services that are highly scalable, manageable, and secure. The databases supported include Oracle Database 10g or above. Integrate EDocGen database mail merge API with ORDS for on-demand document generation.


MySQL & SQL Server Database mail merge

SQL Server, MySQL offer Zapier integration apart from REST data services. If you are a business user, you can use Zapier to connect EDocGen with SQL Server, and MySQL for auto-generating documents. You can set the triggers as per your workflow. For example, every time a new record is inserted/updated a document conforming to your branding guidelines get created and sent for E-Sign.


Conclusion:

EDocGen database mail merge solution automates document generation from almost all leading database engines. Give it a try by registering.

Popular Posts

Oracle Database Mail Merge

Posted by admin

Will you ever fill a document manually, if there is a choice to use a system/tool that auto-