Show SQL Server table inside Nintex Form

  • 4 July 2018
  • 6 replies

Userlevel 7
Badge +17

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: 


The table is ready to be shown:

HTML table out of SQL table

I hope this can be find useful for you.

6 replies

Userlevel 2
Badge +11

Thank you Tomasz Poszytek for this excellent tip.

Userlevel 4
Badge +12

Nice! That surely will be useful.

Userlevel 7
Badge +17

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

Badge +2

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:

<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>')+'

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


Badge +17

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. 

Badge +1

Great job! I liked this post.

I created a Nintex form using the responsive form to connect to a cloud based sql server. When I test the Connection to the server the result is successful and when I test the query I get the expected result. However when I preview the form no results appear in the dropdown.