Proposal Automation Software

Database Mail Merge from Traditional Databases

Introduction


In every organization, database plays an important role in holding entire organization’s data across functions/departments. 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. This explains the popularity of traditional databases.

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 design capabilities.

For organizations creating state-specific/country-specific documents such as insurers, health care institutions and financial institutions etc., manual creation of documents from database records is a costly and time-consuming task. Moreover, the document templates undergo a frequent change that forces associated code changes every time.

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 database mail merge.

EDocGen helps business users to generate documents in bulk as well as on-demand from traditional databases.

  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


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

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

Step1: Exporting XML 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 

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

Step2: Populating XML into Document Template

EDocGen database mail merge supports filling of dynamic text, tables, and images from XML data into document templates. Please visit XML to Word blog for a detailed description on how to go about XML population. 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


MySQL


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.


SQL Server


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


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

The next step is to populate this XML file into template similar to the way we did with Oracle.


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:

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

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:

As you can see, you can use EDocGen database mail merge solution for automating document generation from almost all leading database engines using techniques detailed above

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-