cancel
Showing results for 
Search instead for 
Did you mean: 

Show SQL Server table inside Nintex Form

Automation Master
Automation Master
6 5 2,280

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:

  1. SELECT TOP(1) (SELECT name, lastname, email, role
  2. FROM users
  3. RIGHT JOIN roles ON roles.Id = users.roleId
  4. ORDER BY lastname ASC
  5. 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:

  1. <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:

  1. '<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.

5 Comments
jpmhuls
Nintex Newbie

Thank you Tomasz Poszytek for this excellent tip.

praios81
Nintex Newbie

Nice! That surely will be useful.

Automation Master
Automation Master

You can go even further. Add DataTables script (https://DataTables.net) as custom includes, use proper classes and enjoy dynamic HTML tables, allowing sorting, searching and filtering  

youlearn
Nintex Newbie

Hello Tomasz poszytek

I am having trouble implementing this solution. Can you please help. 

My sql statement is 

Select (Select [IRB ID] as IRBID, [Approval Date] as ApprovalDate , [Expiration Date] as ExpirationDate, [Review Type] as ReviewType From [dbo].[vTrial_IRBApproval] where [Review Type] = 'initial review' FOR XML Path) as datatable 

This works fine and retrives everything in one row. 

 

Calculated Value control formula:

'<table>
<thead><tr><th>IRBID</th><th>ApprovalDate</th><th>ExpirationDate</th><th>ReviewType</th></tr></thead>
<tbody>'+replace(replace(replace(replace(sqlIRBApprl, '</ReviewType><IRBID>', '</td></tr><tr><td>'),'\<\/(?!td|tr)[a-zA-Z]+\>\<(?!td|tr)[a-zA-Z]+\>','</td><td>'), '</ReviewType>', '</td></tr>'), '<IRBID>', '<tr><td>')+'
<tbody>
</table>'

When I use calculated value control with this formaula, entire form goes blank. 

Can you please let me know what I am doing wrong. 

Thank you

 

Community Manager Community Manager
Community Manager

If everything goes blank, that's an indication that the code on the form is corrupt or not correct. I would suggest going at it piece by piece to ensure that your code is loading properly. 

About the Author
SharePoint and Office 365 expert with many years of experience, implementing business solutions following the low-code / no-code approach using Nintex, Microsoft Flow, PowerApps. A fan of automation of modern workplace processes and solutions.