Introduction

SQL Server is helping enterprises to build mission-critical applications with high-performance, in-memory security technology across OLTP (online transaction processing), data warehousing, business intelligence, and analytics. Some of the large ERPs and CRMs are powered by SQL server.

Documents are the universal truth across organizations of all sizes. Proposals, contracts, invoices, compliance documents are all part of business operations. Enterprises use approved document templates for each of these document types. Whereas, a SQL Server database holds all the data that goes into these document templates for documents production.

Re-keying data into templates wastes time and resources. Hence, businesses look to automate document creation. An ideal solution would populate SQL Server data into Word / PDF document templates for bulk/on-demand document creation. Smartly choosing the right approach can save you thousands of dollars.

Document Generation Approach 1 - VBA

One of the popular approaches is to use VBA or VSTO to fill the dynamic fields with database data. Developers code for each dynamic field and fill them with data retrieved from a SQL query. Document templates undergo frequent changes to comply with external and internal standards. Every time, there is template change, corresponding code changes have to be made, making it a maintenance nightmare.

VBA is also used for creating malware and viruses. Hence many organizations have strict policies on usage of VBA code. Moreover, VBA coded templates are not compatible with different versions of Office suite thus limiting its utility beyond the organization. Every time you upgrade the Office version, the templates may require modifications.

Document Generation Approach 2 – Reporting packages

Another popular approach is to use a reporting package such as SSRS that creates documents from SQL Server data. You have to import the document as a default report into each installed instance, thus making it less elegant. Moreover, reporting packages lack rich document design capabilities and are best suited for creating interactive reports only. So if you have a complex invoice or contract template, this approach may not be of much use.

I’m not going to discuss T-SQL stored procedures because they are not a DIY solution for business users and require development resources. Even using the reporting package two approach doesn’t empower business users completely. Hence, there is a need for a business user-friendly solution for SQL to PDF and Word documents creation and document generation software is the perfect solution to go with.

Document Generation Approach 3 – Document Generation Software

With document generation software, business users can use existing Microsoft Word and PDF templates for contracts, reports, letters, etc. in editors and populate them with SQL Server database data. They can edit them in their favorite Word / PDF editors for template changes.

Apart from web app, most of the document generation software also offers a REST API. By integrating with API,

• Document generation can be automated (on-demand document generation) without requiring user interaction
• Create thousands of documents in one go (bulk document generation) right from database

Another salient feature of document generation software is you can use both PDF and Word templates and generate either PDF or Word documents. Let’s examine how to go about this approach.

Document Generation Software in Action

Documents generation from SQL Server is a two-step process.

1. Export SQL Server data in XML/JSON format
2. Populating that XML/JSON into the document template

Step 1: Export table data as XML or JSON

The SELECT statement with "FOR XML" clause in the query retrieves table data as XML. FOR XML clause, you can specify the structure of the XML output explicitly through arguments – RAW, AUTO or EXPLICIT, or let the structure of the SELECT statement determine the output. Below is a simple example of the AUTO argument that retrieves all the records from the Person table.

SELECT Title,
       FirstName,
       MiddleName,                                       
       LastName,
       Suffix
FROM Person.Person
FOR XML AUTO                           

Similarly, export data from SQL Server as JSON by adding the "FOR JSON" clause to a SELECT statement. With the FOR JSON clause, you can specify the structure of the JSON output explicitly through arguments – RAW, AUTO or EXPLICIT, or let the structure of the SELECT statement determine the output.

In SQL Server 2016+s, you can store JSON documents in SQL Server and query JSON data as in a NoSQL database. However, EDocGen accepts any JSON format and no data transformation is necessary.

Below is a simple example of the AUTO argument that retrieves all the records from the Emp table.

SELECT name, surname
FROM emp
FOR JSON AUTO  

The results in JSON.

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

Now with our XML /JSON files ready, next step is to populate XML/JSON into the document template.

Step2: Populate XML /JSON into Document Template

Document generation software, such as EDocGen, supports the population of dynamic text, tables, paragraphs, hyperlinks, and images etc. from XML/JSON data into Word and PDF document templates.

• Upload existing template after adding relevant tags for dynamic fields
• You don't have to do any data transformation and can populate data as-is. Upload XML/JSON file and map dynamic fields to data fields before clicking generate button.
• Email, E-sign or sync cloud storage the generated documents

As mentioned above, you can use API to automate the document creation without manual intervention. Integrating with API allows you to extend the document distributon channels.

Conclusion

In summary, your best approach for document automation from SQL server depends on your needs. If your templates don’t undergo changes and would be used only internally then VBA can be a good choice. If your templates are simple, SSRS also can be a good choice. If you’re looking for flexibility and want to empower your business users, then document generation software is the best fit.