DataTable populated from SQL Server database

Hi Experts,
I found this DataTable example with a hard coded table.
I was wondering if it is possible to populate it from a SQL Server database instead.
If yes, could you please let me know how to modify the code?

<!DOCTYPE html>
<html>
<head>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
        <script type="text/javascript" charset="utf8" src="http://code.jquery.com/jquery-1.12.4.js"></script>
        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function() {
    $('#example').DataTable( {
        "lengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]]
    } );
} );
</script>
</head>

<body>

<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>

        </tbody>
    </table>

</body>
</html>

Open in new window

romsomAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Well, there are things that you can do from SQL Server as far as data is concerned, even presenting as XML:

Have a read of : https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server

And barely touching the edges, a couple of very simple examples
-- step 1 - create some test data to exemplify the XML options / samples
 
create table #my_xml_sample (id int, lname varchar(20), name varchar(20), address1 varchar(20))
insert #my_xml_sample values (1,'eyes','angel','11 the street')
insert #my_xml_sample values (2,'mark','aki','22 the street')
insert #my_xml_sample values (3,'pen','jim','22 the street')
insert #my_xml_sample values (4,'wills','mark','44 the street')
 
 
-- sample 1 - the simple query
 
select lname, id, name, address1 from #my_xml_sample for xml path('Contacts'), root('ContactMaster')
GO
 
-- sample 2 - the simple query but with lname as header tag
 
select lname as "@lname", id , name , address1 from #my_xml_sample for xml path('Contacts'), root('ContactMaster')
GO
 
-- sample 3 - inline group selects
 
select lname as "@LastName", id as "@ID", name as "@Name"
from #my_xml_sample g for xml path('contacts'), root('ContactMaster'), type, elements
GO
 
-- sample 4 - sub group selects
 
select lname as "LastName",
       (select id as "@ID", name as "@NAME", address1 as "@ADDRESS" from #my_xml_sample s where s.lname = g.lname FOR XML PATH ('Individuals'), type, elements)
from #my_xml_sample g group by lname for xml path('Contacts'), root('ContactMaster')
GO
 


select lname as "@LastName",
       (select id as "@ID", name as "@NAME", address1 as "@ADDRESS" from #my_xml_sample s where s.lname = g.lname FOR XML PATH ('Individuals'), type, elements)
from #my_xml_sample g group by lname for xml path('Contacts'), root('ContactMaster')
GO


-- sample 5 - using explicit for tree type XML
 
SELECT 1 as Tag,                                        -- this first one sets up structure only
       NULL as Parent,
       NULL as [ContactMaster!1!Contacts!element],
       NULL as [Contacts!2!Lname!element],
       NULL as [Individuals!3!ID], --!element],
       NULL as [Individuals!3!Name], --!element],
       NULL as [Individuals!3!Address] --!element]
            
UNION ALL
Select distinct 2,1, lname, lname,NULL,NULL,NULL from #my_xml_sample
 
UNION ALL
Select distinct 3,2, lname, lname,id,name,address1 from #my_xml_sample
 
order by 3,4,5
       
FOR XML Explicit
 
GO

Open in new window

For your requirements, I reckon it could be constructed, but would be a little fiddley to begin with. Probably best to use tools such as https://datatables.net/ 

But, it isnt just the data, it is also how users interact. So, inclined to agree with Ryan.
0
 
Ryan ChongCommented:
datatable supports the loading of json data. hence, you probably can generate it via backend scripts and load it to datatable via jquery scripts
0
 
romsomAuthor Commented:
Thank you, I've done some further reading. Your comments were very useful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.