Top
Nintex Forms

Show SQL Server table inside Nintex Form

Table of contents:


Ever wondered how to display SQL table inside your Nintex Form? Indeed, there is the “SQL Request” action, but it only allows you to show data from database as a dropdown, list of options, etc… and always – just a single column.

However there is an easy solution for that. The approach I am using includes usage of the “FOR XML” command in a SELECT statement (source). It is available in SQL Server starting from version 2008. It returns data from a query using an XML format, concatenated in a single row, in a single column. Perfect format to parse it!

Step by step

1. First prepare your SELECT query. Mine is for example:

SELECT TOP(1) (SELECT name, lastname, email, role
FROM users
RIGHT JOIN roles ON roles.Id = users.roleId
ORDER BY lastname ASC
FOR XML) as datatable FROM users

With such statement I am sure, that I will receive just a single row and column, that will return the data in a proper XML format. Each row will be built using the following structure:

<name>value</name><lastname>value</lastname><email>value</email><role>value</role><name>value</name><lastname>value</lastname><email>value</email><role>value</role>...

Put the query in the “SQL Request” control inside your form:

SQL Request action Nintex Forms

Set the field not to be visible. It is not going to be used directly.

2. Now add a “Calculated Value” control. It will be used to get the output from the “SQL Request” and parse it into a valid table. I am using the following formula to achieve it:

'<table class="dataTable"><thead><tr><th>Name</th><th>Lastname</th><th>Email</th><th>Role</th></tr></thead><tbody>'+replace(replace(replace(replace(SQL REQUEST CONTROL NAME, '</role><name>', '</td></tr><tr><td>'),'\<\/(?!td|tr)[a-zA-Z]+\>\<(?!td|tr)[a-zA-Z]+\>','</td><td>'), '</role>', '</td></tr>'), '<name>', '<tr><td>')+'</tbody></table>'

It simply creates a ready to use HTML table. It replaces ending and starting XML tags to starting and ending <tr><td> tags (to mark start and end of each row).

3. Next define a CSS styles for your table. I used the following page to create a set of CSS: https://divtable.com/table-styler/ 

Done!

The table is ready to be shown:

HTML table out of SQL table

I hope this can be find useful for you.


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

3 Comments
  • Karen Cuenco

    Hi Tomasz,

    Is this also available using Nintex Forms for Office 365? Thanks.

    December 3, 2020 at 7:47 am Reply
    • Tomasz Poszytek

      Unfortunately for Nintex Forms for Office 365 there is no such control that would let you to perform a direct query to SQL Server. You would need to make a quite complex set up made from JavaScript, possibly Azure Functions etc… No easy way.

      December 8, 2020 at 4:19 pm Reply
  • Vishal Shyam

    Hi,
    Can you please check my html code? I am getting nothing.
    Thanks!

    April 19, 2021 at 3:48 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.