Proposal Automation Software

SQL PDF - Export data into documents

SQL PDF


SQL Server's ease of use, availability and tight integration with Windows operating system 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. Some of the large ERPs and CRMs are powered by SQL server.

Businesses create a variety of documents in PDF format using SQL server data. They populate SQL data into PDF templates for bulk or on-demand document creation. But SQL PDF document creation is tricky and complex.


Different approaches for SQL PDF documents


One of the popular approaches is to create word template with placeholders. Then use VBA or VSTO to parse the XML representation to populate these placeholders or iterate over them using a data source. Then convert the created Word documents into PDF format.

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

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

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

With EDocGen, business users can create/edit contracts, reports, letters etc. templates in Microsoft Word or PDF editors. Then they can populate them with SQL server database data to create PDF documents..

We're going to discuss SQL PDF generation with JSON and XML data population. They have the ability to include schema information along with the data which is an advantage over CSV files.


SQL PDF -XML data


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

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

Step1: Export table data as XML

Retrieving data from SQL Server in XML format can 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. It supports filling of dynamic text, tables, paragraphs, hyperlinks and images from XML data into document templates. Please visit XML to PDF blog for a detailed description of how to go about XML population. In short,

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. Select PDF as output format and click generate button. The system generates a ZIP that contains generated PDF documents


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

SQL PDF Document -JSON


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

  1. Exporting SQL Server DB data into JSON
  2. Populating that JSON into fillable PDF or 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. It supports filling of dynamic text, tables, paragraphs, hyperlinks and images from JSON data into document templates. Please visit JSON to PDF 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 after selecting PDF as output format



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 PDF documents creation.

Popular Posts