Proposal Automation Software

Generate PDF Documents from SQL Server

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

SQL PDF


Businesses create a variety of documents in PDF format from SQL Server/MySQL/Oracle 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, thus are maintenance-heavy.
  2. The more complex the PDF, the harder it gets to generate. Often, they don’t support your company styles/fonts.

EDocGen is the simplest and best approach for creating PDF documents from SQL data. 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.

SQL Database Selection

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


SQL Database Connection

In the query you can use native SELECT statements. The system supports complex queries including SQL joins.

SELECT 
    first_name,
    last_name,
    employees.department_id,
    departments.department_id,
    department_name
FROM
    employees
        INNER JOIN
    departments ON departments.department_id = employees.department_id
WHERE
    employees.department_id IN (1 , 2, 3);

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.

Tables

The system supports the creation of tables, loops, and lists from DB data. You can also create nested tables up to 6-levels. Take the example of "Order table".

Sales Order Order Status
 12345  5
 --  --

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  

Images 

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

Content Blocks/Sub-templates

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

HTML

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 system supports the filling of dynamic text, tables, paragraphs, blobs, hyperlinks, and images from SQL Server data into existing PDF/Word templates.

Also, 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 the names match, the system automatically maps them for you. Thus, database changes would never affect the PDF generation.


SQL Database Connection

After mapping, select PDF as output format and click the generate button. The system generates a ZIP that contains generated PDF documents


Step3: Document Distribution

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.


SQL Document distribution

Working with complex templates


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


SQL Word templates

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 download the SQL Server data in XML/JSON format and use it for document generation. Please visit XML to PDF and JSON to PDF for a detailed description of how to go about the XML/JSON population.

EDocGen is an API-first product. This entire document generation process can be automated by integrating with EDocGen API. . You can automate the bulk/on-demand PDF generation from SQL Server. You can also create a workflow. In summary, EDocGen document generation software is the perfect solution for SQL to PDF documents creation.

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

Popular Posts