I trying to display a table which is the output of an SQL request control in the form. I am using this blog post
1. I use SQL request to get the output table in to xml. When I run the SQL Query I get a column (datatable) with one row containing the output in xml.
SQL : Select (Select [IRB ID], [Approval Date], [Expiration Date], [Review Type] From [dbo].[vTrial_IRBApproval] FOR XML AUTO) as datatable
2. So I have four columns and I want to display these on the form.
3. I want to use the calculated value control to parse this output in a table. I need help with the formula please. Below is what I got and it is not working.
<table class="dataTable"><thead><tr><th>IRBID</th><th>ApprovalDate</th><th>ExpirationDate</th><th>ReviewType</th></tr></thead> <tbody>'+replace(replace(replace(replace(sqlIRBApprl, '</IRB ID><name>', '</td></tr><tr><td>'),'\<\/(?!td|tr)[a-zA-Z]+\>\<(?!td|tr)[a-zA-Z]+\>','</td><td>'), '</IRBID>', '</td></tr>'), '<Review Type>', '<tr><td>')+'</tbody></table>