SQL Word

SQL to Word and PDF - Docs from SQL Server

Generate Word/PDF documents from SQL Server, Oracle, and MySQL using SQL queries.

SQL 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 the 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 the data that goes into these document templates for business documents production.

Re-key data into templates wastes time and resources. Hence, businesses look to automate document creation from SQL data. 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 WORD Population

Different approaches for SQL to DOCX Generation

SQL Server to WOrd

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 a template change, corresponding code changes have to be made, making it maintenance heavy.

VBA is also used for creating malware and viruses. Hence many organizations have strict policies on the usage of VBA code. Moreover, VBA coded templates are not compatible with different versions of the Office suite. This limits its utility beyond the organization. Every time you upgrade the 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 MSWord and PDF documents without the 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

It is designed for business users to generate business documents without IT support. Generate documents from SQL Server in a simple 2-step process.

  1. Connect to your database from the system.
  2. Generate documents from query data.
  3. Optionally, you can choose to send generated documents to individual recipients.

Step1: Connect to the database

In the connection screen, select the SQL server from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password.

SQL Database Connection

For the SELECT query, use native SQL query statements. The system supports all types of queries including SQL joins.

	department_id IN (1, 2, 3)
        INNER JOIN
    departments ON departments.department_id = employees.department_id
    employees.department_id IN (1 , 2, 3);

After entering all mandatory fields, click on the "Connect" button to check the connection. In the next step, the system populates query data into your template and generates the documents.

Step2: Generate Documents

The system supports the filling of dynamic text, tables, paragraphs, hyperlinks, blobs, and images from SQL data into document templates. Please refer to how-to generate for more details.

You don't have to do any data transformation and can populate data as-is. You have an option to map the template's dynamic fields to the query columns. If both the names match, the system automatically maps them for you. Thus, database changes don't affect document creation.

Enter the file name and prefix for individual documents before clicking the generate button. The output in ZIP format would contain generated documents

Step3: Document Distribution

You have an option to send generated documents individually by mail as an attachment. Select the email column in the query, input your mail subject & body, and click the send button. The system auto-sends these emails from your business email.

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.

Complex Documents Generation

Templates vary in complexity. Often, you find templates with multiple tables, nested tables, lists, etc. Below is the sample template.

SQL Word templates

You can generate documents from it with the same above approach. You only need to change the SQL queries to retrieve the data in XML/JSON format.

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 the 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 the 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
	"name": "John"
	}, {
	"name": "Jane",
	"surname": "Doe"

If you don't want to enter connection details for security reasons, you may download the SQL Server data in XML/JSON format and use it for document generation. Please refer to XML to Word and JSON to Word detail how to go about the XML and JSON population.

Schedule a demo to explore SQL to Word generation.

Popular Posts