SQL Word

SQL to Word and PDF - Docs from SQL Server

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

SQL Word


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 the SQL server.

Documents are the universal truth across organizations of all sizes. Proposals, contracts, invoices, compliance documents are all part of business operations. Enterprises use approved document templates for each of these document types. Whereas, SQL Server DB holds the data that goes into these document templates for business documents production.

Re-key data into templates wastes time and resources. Hence, businesses look to automate document creation from SQL data. An ideal solution would populate SQL Server data into Word/PDF document templates for Docx and PDF documents generation. It also should allow bulk/on-demand document creation. Smartly choosing the right approach can save you thousands of dollars and countless hours.


SQL Server WORD Population



SQL to Word with EDocGen


It is designed for business users to generate business documents without IT support. Generate documents from SQL Server in a simple 2-step process.

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

Step1: Connect to the database

In the connection screen, select the SQL server from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password.

SQL Database Connection

For the SELECT query, use native SQL query statements. The system supports all types of 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);

Business documents are complex. Most of them would need the population of tables, images, HTML, Blob, etc. The system supports all these scenarios. You only need to change the SQL queries to fetch the data in XML/JSON format.

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 fetches 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 fetches 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

You can store the images in Base64 format or external image URLs. Include these columns in your SELECT query for the population.

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.



After entering all mandatory fields, click on the "Connect" button to check the connection. In the next step, the system populates query data into your template and generates the documents.


Step2: Generate Documents

The system supports the filling of dynamic text, tables, paragraphs, hyperlinks, blobs, and images from SQL data into document templates. Please refer to how-to generate for more details.

You don't have to do any data transformation and can populate data as-is. You have 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 don't affect document creation.

Enter the file name and prefix for individual documents before clicking the generate button. The output in ZIP format would contain generated documents


Step3: Document Distribution

You have an option to send generated documents individually by mail as an attachment. Select the email column in the query, input your mail subject & body, and click the send button. The system auto-sends these emails from your business email.

Apart from the web app, EDocGen also offers REST API for SQL to Word generation. By integrating with API,

  1. Document generation can be automated (on-demand document generation) without requiring user interaction
  2. Create thousands of documents in one go (bulk document generation) right from the SQL Server database

Another salient feature of EDocGen is you can use

both PDF and Word templates and generate either PDF or Word documents.

Complex Documents Generation


Templates vary in complexity. Often, you find templates with multiple tables, nested tables, lists, etc. Below is the sample template.


SQL Word templates

You can generate documents from it with the same above approach. You only need to change the SQL queries to retrieve the data in XML/JSON format.

The SELECT statement with "FOR XML" clause in the query retrieves table data as XML. FOR XML clause, you can specify the structure of the XML output explicitly through arguments – RAW, AUTO, or EXPLICIT, or let the structure of the SELECT statement determine the output.

Similarly, export data from SQL Server as JSON by adding the "FOR JSON" clause to the SELECT statement. FOR JSON clause, you can specify the structure of the JSON output explicitly through arguments – RAW, AUTO, or EXPLICIT, or let the structure of the SELECT statement determine the output.

JSON supports null values as well as the bit values true and false. Unlike CSV, you can distinguish between a blank string and a null one. In the latest SQL server editions, you can store JSON documents in SQL Server and query JSON data 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 refer to XML to Word and JSON to Word detail how to go about the XML and JSON population.


Schedule a demo to explore SQL to Word generation.


Popular Posts