Skip to main content

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)da-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.

Thank you Tomasz Poszytek for this excellent tip.

Nice! That surely will be useful.

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  

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

 

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. 

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.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings