SQL Word

SQL to Word and PDF - Docs from SQL Server

SQL to Word


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 docx and PDF documents generation. It also should allow bulk/on-demand document creation. Smartly choosing the right approach can save you thousands of dollars and countless hours.


SQL Server to MS Word



Different approaches for SQL to Docx Generation


Approach 1 –VBA

One of the popular approaches for SQL to word is using VBA or VSTO to fill the dynamic fields with database data. Developers code for each dynamic field and fill them with data retrieved from the 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 Microsoft SQL Server Reporting Services or SAP Crystal Reports. Reporting packages offer only limited document design and are best suited for creating interactive reports. If you have a complex invoice or contract template, this approach may not be of much use.

Moreover, they require heavy server resources to run. You also have to import them into each of the installed instances.

Both of these are not DIY solutions and require development resources thus making them maintenance heavy. If you're looking for a business user-friendly solution for SQL to Word, EDocGen is the perfect solution.

Approach 3 – EDocGen Document generation software

It is a cross-platform solution without limited designers or proprietary report formats. It offers a flexible way to generate docx and PDF documents without learning curve.

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

Let’s examine how to go about this approach.


SQL to Word with EDocGen


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

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,					
	FirstName,
	MiddleName,					
	LastName,
	Suffix
FROM Person.Person
FOR XML AUTO
GO			      

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.

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. In latest SQL server editions, you can store JSON documents in SQL Server and query JSON data as in a NoSQL database.

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   
Results
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

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

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

Apart from the web app, EDocGen also offers REST API for SQL to Word generation. 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 the SQL Server database

Another salient feature of EDocGen is you can use

both PDF and Word templates and generate either PDF or Word documents.

Schedule a demo to explore SQL to Word generation with EDocGen


Popular Posts