Generate PDF Documents from SQL Server

SQL PDF

Businesses create a variety of documents in PDF file format from SQL Server/Oracle/MySQL data. They populate SQL data into PDF/Word templates for creating business documents in PDF format. But SQL PDF document creation is tricky and complex. Almost all popular approaches are not business user-friendly.

1. Building the PDF layout is very tedious. It’s not easy to introduce a new template or make changes to an existing template. These require code changes and thus are maintenance-heavy.
2. The more complex the PDF, the harder it gets to generate. Often, they don’t support your company's styles/fonts.

EDocGen is the simplest and best approach for creating PDF documents from SQL file. You can populate SQL data into existing Word or PDF templates as-is. The system supports the creation of password-protected PDFs with watermarks.

SQL to PDF Documents Generation

Using EDocGen, the generation of PDF documents from SQL Server is a simple two-step process.

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

Business users can populate DB data into existing templates, without IT intervention. Since templates are in PDF/Word/PPTX format, business users can edit them in native editors. No separate plug-in/add-on is needed.

Step1: Connect to the database

In the connection screen, select the "SQL Server" option from the drop-down.

Then enter the connection URL in the "username@hostname[:port]/DatabaseName" format, password, and the native SQL query.

In the query, you can use native SELECT statements. Below are the sample MSWord template and the associated query for populating it. The system populates the table, image, and dynamic text fields in the template.

SELECT
  invoice.inv_no,
  `logo`,
  `date`,
  `trms_pymnt`,
  `po_ref`,
  `name`,
  `address`,
  `contact`,
  `Email`,
  `Phone`,
  `myhtml`,
  para,
  CONCAT(
    '[',
    GROUP_CONCAT(
      CONCAT(
        '{"amnt":"',
        amnt,
        '", "descrptn":"',
        descrptn,
        '"}'
      )
    ),
    ']'
  ) IT
FROM
  `invoice`,
  `inv_item`
WHERE
  invoice.inv_no = inv_item.inv_no
Group BY
  invoice.inv_no;

The system supports complex queries including SQL joins.

The system supports the creation of tables, loops, and lists from DB data. You can also create nested tables of up to 6 levels. If your template has many tables, nested tables, lists, etc., then fetching data in JSON and XML formats through SQL queries is the best approach. Take the example of "Order table".

Below is the query that populates this table. It retrieves the data in XML format.

SELECT Cust.CustomerID,     
       OrderHeader.SalesOrderID,   
       OrderHeader.Status,  
       Cust.CustomerName  
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader  
WHERE Cust.CustomerID = OrderHeader.CustomerID  
ORDER BY Cust.CustomerID  
FOR XML AUTO, ELEMENTS  	      

Similarly, the below query retrieves the data in JSON format.

SELECT CustomerID,  
      CustomerName,  
      (SELECT SalesOrderID, Status  
       FROM salesOrderHeader AS D  
       WHERE H.CustomerID = D.CustomerID
       FOR JSON PATH) AS D  
FROM Customer AS H  
FOR JSON PATH  

Fetch them through SELECT statements for the population. They can be in Base64 format or image URLs.

You can store them as Blobs and include them in your SELECT statements.

The system supports the population of HTML, graphs&charts.

The next step is to populate query data into the document template.

Step2: Populate DB Data into Document Template

The data population doesn't require any data transformation. Business users can populate data as-is. There is an option to map the template's dynamic fields to the query columns. If both names match, the system automatically maps them for you. In the above example, you would notice that the template's tag names match with database column names. Thus, database changes would never affect PDF generation.

After mapping, select PDF as the output format and click the generate button. The system generates a ZIP that contains generated PDF documents. You can also encrypt the PDFs and create watermarks.

Step3: Document Distribution

This is an optional step. The system allows you to send generated individual documents from your business email. In the distribution screen, select the column for the email field in the drop-down. Then enter the custom email subject and email body. On clicking generate, the system sends the documents to individual recipients as an attachment. You can also use the dynamic fields in the email body and subject. Apart from Email, the system supports other distribution channels including print, sync to cloud storage, and document archival.

Working with complex templates

Below is an example of a complex template with nested tables and nested loops.

Table data as XML

Fetching data from SQL Server in XML format can be achieved via the SELECT statement. It is used regularly 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.

Table data as JSON

Similarly, fetch data from SQL Server as JSON by adding the "FOR JSON" clause to the 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, The system accepts any valid JSON and no data transformation is necessary.

In the latest editions of SQL servers, you can store and query JSON documents as in a NoSQL database.

If you don't want to enter connection details for security reasons, you may export data from the SQL Server in XML/JSON format and use it for document generation. Please visit Azure SQL Server PDF generation for a detailed description of how to go about it.

Automation

If you use the same template and query every time you create the documents, entering the information is time-consuming and tedious. The system provides options to automate these steps.

To create automation, follow the above steps. After that, the generation of PDF documents is as simple as clicking a button.

Also, the system offers options to schedule the generation every day/month/year.

There is also an option to automatically create printer-friendly PCL5 and PostScript files and upload them to an FTP server.

EDocGen is an API-first product. This entire document generation process can be automated by integrating with EDocGen REST API. You can automate the bulk/on-demand PDF generation from SQL Server.

In summary, EDocGen document generation software is the perfect solution for SQL to PDF documents creation. It supports the filling of dynamic text, tables, paragraphs, blobs, hyperlinks, and image files from SQL Server data into existing PDF/Word templates.

It is highly secure and integrates into your ecosystem. Schedule a demo for more details.