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:
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:
I hope this can be find useful for you.
