Proposal Automation Software

SQL to Word Documents - Export data into documents

Introduction


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 to 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 to 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.

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 these not DIY solutions but require development resources. 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 developer’s time is difficult and expensive. Hence, there is a need for a business user-friendly solution for SQL to Word documents creation and EDocGen is the perfect solution to go with.

With EDocGen, business users can edit Microsoft Word templates (contracts, reports, letters etc) using the Word editor and populate them with XML or JSON data from your SQL server database.


SQL to Word Document -XML


This is a two-step process. You can automate it by integrating with EDocGen document generation REST API.

  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 has the clause FOR XML, thus easily converting table data into XML nodes. FOR XML takes different arguments – RAW, AUTO or EXPLICIT.

Below is a simple example with 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

· Enhancements to OPENROWSET to allow bulk loading of XML data.

· The OPENXML function, to retrieve XML data in relational format

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, 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 tags for dynamic fields. Alternatively, you can add tags through EDocGen editor after uploading the template

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

3. Download generated documents


SQL to Word Document -JSON


Again, this 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 JSON

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.

Format query results as JSON, or export data from SQL Server as JSON, by adding the FOR JSON clause to a 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. 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"
	}]

Step2: Populating JSON into Document Template

EDocGen is Windows OpenXML based solution that supports filling of dynamic text, tables, 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. Alternatively, you can add tags through EDocGen editor after uploading the template

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

3. Download generated documents



You can automate the bulk/on-demand document genration 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 to Word documents creation.

EDocGen provides a provision to save generated documents as PDFs. It also allows population of fillable PDF templates with SQL data and process is very similar to Word documents creation. So using EDocGen, you can create PDF documents also from Word and PDF templates.

Popular Posts