Document Generation From Traditional Databases
Document Generation From Traditional Databases
A DIY document generation system reduces dependency on the IT department and provides the flexibility to change templates as and when required.
Join the DZone community and get the full member experience.Join For Free
Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.
In every organization, database plays an important role in holding the entire organization’s data across functions/departments. Traditional databases such as IBM, Microsoft, and Oracle control close to 80% of DBMS market with Oracle leading the pack. Oracle, MySQL, and SQL Server occupy the top three positions in the 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- or country-specific documents such as insurers, healthcare institutions, financial institutions, etc., the manual creation of documents from database records is a costly and time-consuming task. Moreover, document templates undergo frequent changes that force associated code changes every time.
Hence, it’s a good idea for business users to use a DIY document generation system such as EDocGen. This reduces their dependency on the IT department and provides the flexibility to change templates as and when required.
A document generation system helps business users generate documents in bulk as well as on-demand from traditional databases.
Bulk document creation: Produce high volumes of documents on a scheduled basis or interactively.
On-demand document creation: Real-time demand document creation as part of the work flow. For example: 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 three database engines. You can extrapolate this to other traditional relational databases.
Bulk Document Generation
You can generate documents from Oracle database in two steps.
- Execute SQL to retrieve data in XML format. Database administrators can create a cron job to automate this.
- Populate this XML into templates.
Step 1: 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 the template changes. System admins (DBAs) and many business users are adept at SQL. The following example explains how to export Oracle database data as XML using
SELECT XMLAgg (XMLElement(“Department”,XMLAttributes( department_id as “ID”, department_name)) FROM departments
Armed with XML, the next step is to populate this XML into the document template.
Step 2: Populating XML Into Document Template
Fill dynamic text, tables, and images from XML data into document templates using document generation system. Briefly,
Add relevant tags to dynamic fields in the template and upload it.
Populate it with XML by uploading XML and clicking Generate.
Download generated documents.
MySQL has no built-in support for creating XML in its SQL implementation. So, we take a different approach. Document generation systems support 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 a JSON file, as shown below, using the
SELECT CONCAT('[',GROUP_CONCAT(JSON_OBJECT('ID', department_id, 'name', department_name)), ']) FROM departments
The next step is to populate the CSV/JSON file into a template, similar to the way we did with Oracle.
Like Oracle, MSSQL also allows you to retrieve data in XML format. Use the XML
RAW function to generate XML documents 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 the template similar to the way we did with Oracle.
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 that your web visitor fills a form to receive a proposal document.
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 document generation system API with ORDS for on-demand document generation.
MySQL and SQL Server
SQL Server and MySQL offer Zapier integration apart from REST data services. If you are a business user, you can use Zapier to connect your document generation system 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 gets created and sent for an e-signature.
Opinions expressed by DZone contributors are their own.