Proposal Automation Software

SQL to Word Document- Docs from SQL Server DB

SQL to Word Document

SQL Server is helping enterprises to build mission-critical applications with high-performance, in-memory security technology. 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, SQL Server DB holds all the data that goes into these document templates for documents production.

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

SQL to Word Document

Different approaches for SQL to PDF and Word documents

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 SQL query. Document templates undergo frequent changes to comply with external and internal standards. So every time, there is template change, corresponding code changes have to be made, making it a maintenance heavy.

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. This limits its utility beyond the organization. Every time you upgrade office version, they may require modifications.

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 it as a default report into each installed instance thus making it less elegant. Moreover, reporting packages lack in document design and are best suited for creating interactive reports. So if you have a complex invoice or contract template, this approach may not be of much use.

Both of these are not DIY solutions and require development resources thus making them maintenance heavy. Hence, there is a need for a business user-friendly solution for SQL Word documents creation. Document generation software such as EDocGen is the perfect solution to go with.

Approach 3 – Document generation software

With EDocGen, business users can use existing Microsoft Word and PDF templates for contracts, reports, letters etc. generation. They can edit them in their favorite Word / PDF editors. Using DIY interface, they then can populate templates with SQL server database data. No data transformation is necessary.

Apart from web app, EDocGen also offer REST API. By integrating with API,

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

Another salient feature of EDocGen 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.

SQL to PDF and Word Documents

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

JSON supports null values as well as the bit values true and false. Unlike CSV, you can distinguish between a blank string and a null one.

Step1: 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,					
FROM Person.Person

Similarly, export data from SQL Server as JSON by adding the "FOR JSON" clause to SELECT statement. 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 latest SQL server editions, 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
	"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

EDocGen is Windows OpenXML based solution. It supports filling of dynamic text, tables, paragraphs, hyperlinks, and images from XML data into document templates. Please visit XML to Word JSON to Wordblogs for a detailed description of how to go about XML and JSON population. Briefly,

1. Upload existing template after adding relevant tags for dynamic fields

2. You don't have to do any data transformation and can populate data as-is. Upload XML file and map dynamic fields to nodes before clicking generate button. The output in ZIP format would contain generated documents

As mentioned above, entire document generation process can be automated by integrating with EDocGen API.

Popular Posts