Proposal Automation Software

SQL Word - Export data into documents

SQL Word


SQL Server's ease of use, availability and tight Windows operating system integration makes it an easy choice for firms that choose Microsoft products for their enterprises. It is the platform for both on-premises and cloud databases and business intelligence solutions.

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.

For contracts, reports, letters etc. creation, businesses use SQL server data to create documents. They populate SQL data into Word document templates for bulk/on-demand document creation. But SQL Word document creation is tricky and if you search on Google, you’ll see lots of people asking for help in this regard.


Different approaches for SQL Word documents


One of the popular approaches is to create word template with placeholders and use VBA or VSTO to parse the XML representation to populate these placeholders or iterate over them using a data source.

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

The problem with word templates is, they undergo changes frequently. So for every template change, you’ve to seek developer’s time to make corresponding changes. However, getting a developer’s time is difficult and expensive.

Another popular approach is to use a reporting package such as SSRS that creates Word 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 only. 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 and EDocGen is the perfect solution to go with.

With EDocGen, business users can create/edit document templates such as contracts, reports, letters etc. in Microsoft Word editor and populate them with SQL server database data in XML or JSON format.


SQL Word -XML data


SQL Word documents generation with XML data is a two-step process.

  1. Exporting SQL Server DB data into XML
  2. Populating that XML into Word document template

Step1: Export table data as XML

Retrieving data from SQL Server in XML format can easily be achieved via the SELECT statement used on a daily basis by DBAs and business users alike. The SELECT statement with "FOR XML" clause in the query retrieves table data as XML nodes. FOR XML takes different arguments – RAW, AUTO or EXPLICIT.

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			      

Apart from FOR XML clause, the other ways to create XML from SQL server include SQL Server export tool (BCP) that creates XML files.

Now with our XML ready, next step is to populate this XML into document template using EDocGen.


Step2: Populate XML into Document Template

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

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

2. With EDocGen, 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 Word documents


This entire document generation process can be automated by integrating with EDocGen API.

SQL Word Document -JSON


SQL to Word documents with JSON data is a two-step process as well.

  1. Exporting SQL Server DB data into JSON
  2. Populating that JSON into Word document template

Step1: Export table data as JSON

Export data from SQL Server as JSON by adding the "FOR JSON" clause to SELECT statement. When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties. FOR JSON takes different arguments – RAW, AUTO or EXPLICIT. To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO. However, EDocGen accepts any JSON format and no data transformation is necessary.

SELECT name, surname
FROM emp
FOR JSON AUTO   
Results
JSON
	[{
	"name": "John"
	}, {
	"name": "Jane",
	"surname": "Doe"
	}]

In latest SQL server editions, you can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.


Step2: Populating JSON into Document Template

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

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

2. Populate it with JSON by uploading JSON and click generate



You can automate the bulk/on-demand document generation by integrating with EDocGen API. Not just that. You can also create a workflow by integrating EDocGen with SQL Server using Zapier. In summary, EDocGen document generation software is the perfect solution for SQL Word documents creation.

EDocGen provides a provision to save generated documents as PDFs. It also allows the population of fillable PDF templates with SQL data and process is very similar to Word documents creation.

Popular Posts