jQuery DataTable Data Sources

DataTable Data Sources

The DataTable plugin can be bound to multiple types of data collections including arrays, xml, json, tsv, csv or remote data. To data bind the DataTable to a data source you need to set its source property to point to an instance of jqxDataAdapter.

The source object represents a set of key/value pairs.

url: A string containing the URL to which the request is sent.
data: Data to be sent to the server.
localData: data array or data string pointing to a local data source.
dataType: the data's type. Possible values: 'xml', 'json', 'jsonp', 'tsv', 'csv', 'local', 'array', 'observablearray'.
type: The type of request to make ("POST" or "GET"), default is "GET".
id: A string containing the Id data field.
root: A string describing where the data begins and all other loops begin from this element.

record: A string describing the information for a particular record.


var source =
{
 dataType: "xml",
 dataFields: [
 { name: 'ShippedDate', map: 'm\\:properties>d\\:ShippedDate', type: 'date' },
 { name: 'Freight', map: 'm\\:properties>d\\:Freight', type: 'float' },
 { name: 'ShipName', map: 'm\\:properties>d\\:ShipName' },
 { name: 'ShipAddress', map: 'm\\:properties>d\\:ShipAddress' },
 { name: 'ShipCity', map: 'm\\:properties>d\\:ShipCity' },
 { name: 'ShipCountry', map: 'm\\:properties>d\\:ShipCountry' }
 ],
 root: "entry",
 record: "content",
 id: 'm\\:properties>d\\:OrderID',
 url: 'orders.xml'
};

XML Data


<entry>
 <id>http://services.odata.org/Northwind/Northwind.svc/Orders(10248)</id>
 <title type="text"></title>
 <updated>2011年12月01日T11:55:06Z</updated>
 <author>
 <name />
 </author>
 <link rel="edit" title="Order" href="Orders(10248)" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Customer" type="application/atom+xml;type=entry" title="Customer" href="Orders(10248)/Customer" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Employee" type="application/atom+xml;type=entry" title="Employee" href="Orders(10248)/Employee" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Order_Details" type="application/atom+xml;type=feed" title="Order_Details" href="Orders(10248)/Order_Details" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Shipper" type="application/atom+xml;type=entry" title="Shipper" href="Orders(10248)/Shipper" />
 <category term="NorthwindModel.Order" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
 <content type="application/xml">
 <m:properties>
 <d:OrderID m:type="Edm.Int32">10248</d:OrderID>
 <d:CustomerID>VINET</d:CustomerID>
 <d:EmployeeID m:type="Edm.Int32">5</d:EmployeeID>
 <d:OrderDate m:type="Edm.DateTime">1996年07月04日T00:00:00</d:OrderDate>
 <d:RequiredDate m:type="Edm.DateTime">1996年08月01日T00:00:00</d:RequiredDate>
 <d:ShippedDate m:type="Edm.DateTime">1996年07月16日T00:00:00</d:ShippedDate>
 <d:ShipVia m:type="Edm.Int32">3</d:ShipVia>
 <d:Freight m:type="Edm.Decimal">32.3800</d:Freight>
 <d:ShipName>Vins et alcools Chevalier</d:ShipName>
 <d:ShipAddress>59 rue de l'Abbaye</d:ShipAddress>
 <d:ShipCity>Reims</d:ShipCity>
 <d:ShipRegion m:null="true" />
 <d:ShipPostalCode>51100</d:ShipPostalCode>
 <d:ShipCountry>France</d:ShipCountry>
 </m:properties>
 </content>
</entry>
<entry>
 <id>http://services.odata.org/Northwind/Northwind.svc/Orders(10249)</id>
 <title type="text"></title>
 <updated>2011年12月01日T11:55:06Z</updated>
 <author>
 <name />
 </author>
 <link rel="edit" title="Order" href="Orders(10249)" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Customer" type="application/atom+xml;type=entry" title="Customer" href="Orders(10249)/Customer" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Employee" type="application/atom+xml;type=entry" title="Employee" href="Orders(10249)/Employee" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Order_Details" type="application/atom+xml;type=feed" title="Order_Details" href="Orders(10249)/Order_Details" />
 <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Shipper" type="application/atom+xml;type=entry" title="Shipper" href="Orders(10249)/Shipper" />
 <category term="NorthwindModel.Order" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
 <content type="application/xml">
 <m:properties>
 <d:OrderID m:type="Edm.Int32">10249</d:OrderID>
 <d:CustomerID>TOMSP</d:CustomerID>
 <d:EmployeeID m:type="Edm.Int32">6</d:EmployeeID>
 <d:OrderDate m:type="Edm.DateTime">1996年07月05日T00:00:00</d:OrderDate>
 <d:RequiredDate m:type="Edm.DateTime">1996年08月16日T00:00:00</d:RequiredDate>
 <d:ShippedDate m:type="Edm.DateTime">1996年07月10日T00:00:00</d:ShippedDate>
 <d:ShipVia m:type="Edm.Int32">1</d:ShipVia>
 <d:Freight m:type="Edm.Decimal">11.6100</d:Freight>
 <d:ShipName>Toms Spezialitäten</d:ShipName>
 <d:ShipAddress>Luisenstr. 48</d:ShipAddress>
 <d:ShipCity>Münster</d:ShipCity>
 <d:ShipRegion m:null="true" />
 <d:ShipPostalCode>44087</d:ShipPostalCode>
 <d:ShipCountry>Germany</d:ShipCountry>
 </m:properties>
 </content>
</entry>

dataFields: An array describing the fields in a particular record. Each data field must define the following members:

name - A string containing the data field's name.
type - A string containing the data field's type. Possible values: 'string', 'date', 'number', 'float', 'int', 'bool'.
format(optional) - Sets the data formatting. By setting the format, the jqxDataAdapter plug-in will try to format the data before loading it.
Example: { name: 'SubmitDate', type: 'date', format: "yyyy-MM-ddTHH:mm:ss-HH:mm" }
columnDelimiter(optional) - specifies the column delimiter when the data source is 'tab' or 'csv'. The default value for 'tab' is '\t' and the default value for 'csv' is ','.
rowDelimiter(optional) - specifies the rows delimiter when the data source is 'tab' or 'csv'. The default value is '\n'.
mapChar(optional) - specifies the mapping char. By default it is '>'.

map(optional) - A mapping to the data field.

Loading XML Data


{ name: 'CompanyName', map: 'm\\:properties>d\\:CompanyName' }

XML Data


<content type="application/xml">
 <m:properties>
 <d:CustomerID>ALFKI</d:CustomerID>
 <d:CompanyName>Alfreds Futterkiste</d:CompanyName>
 <d:ContactName>Maria Anders</d:ContactName>
 <d:ContactTitle>Sales Representative</d:ContactTitle>
 <d:Address>Obere Str. 57</d:Address>
 <d:City>Berlin</d:City>
 <d:Region m:null="true" />
 <d:PostalCode>12209</d:PostalCode>
 <d:Country>Germany</d:Country>
 <d:Phone>030-0074321</d:Phone>
 <d:Fax>030-0076545</d:Fax>
 </m:properties>
 </content>

Loading Nested JSON Data


var data = [{ "empName": "test", "age": "67", "department": { "id": "1234", "name": "Sales" }, "author": "ravi"}];
// prepare the data
var source =
{
 dataType: "json",
 dataFields: [
 { name: 'empName' },
 { name: 'age' },
 { name: 'id', map: 'department>id' },
 { name: 'name', map: 'department>name' },
 { name: 'author' }
 ],
 localData: data
};
var dataAdapter = new $.jqx.dataAdapter(source);

Loading data with XML Attributes

XML Data


<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Customers>
<Customer CustomerID="1" Name="Customer 1"></Customer>
<Customer CustomerID="2" Name="Customer 2"></Customer>
</Customers>

Initialize the jqxDataAdapter plug-in.


var url = "../sampledata/customers.xml";
// prepare the data
var source =
{
 datatype: "xml",
 datafields: [
 { name: 'CustomerID', map: '[CustomerID]' },
 { name: 'Name', map: '[Name]' }
 ],
 root: "Customers",
 record: "Customer",
 url: url
};
var dataAdapter = new $.jqx.dataAdapter(source);

Initialize the jqxDataTable.


$("#dataTable").jqxDataTable(
{ 
	width: 400, 
	source: dataAdapter, 
	columns: [ 
		{ text: 'ID', dataField: 'CustomerID', width: 250 }, 
		{ text: 'Name', dataField: 'Name', width: 150 } 
	] 
});

Loading Nested JSON Data


var data = [
 {
 "state": {
 "id": "x526",
 "city": {
 "name": "Beverly Hills",
 "id": 90210,
 "value": "Beverly Hills"
 }
 }
 }
];
var source = {
 dataType: 'json',
 localData: data,
 dataFields:
 [
 {
 name: 'cityName', map: 'state>city>name'
 }
 ]
};
var dataAdapter = new $.jqx.dataAdapter(source);

values - determines the foreign collection associated to the data field.

Join Two Data Sources


var employeesSource =
 {
 datatype: "xml",
 datafields: [
 { name: 'FirstName', type: 'string' },
 { name: 'LastName', type: 'string' }
 ],
 root: "Employees",
 record: "Employee",
 id: 'EmployeeID',
 url: "../sampledata/employees.xml",
 async: false
 };
 var employeesAdapter = new $.jqx.dataAdapter(employeesSource, {
 autoBind: true,
 beforeLoadComplete: function (records) {
 var data = new Array();
 // update the loaded records. Dynamically add EmployeeName and EmployeeID fields. 
 for (var i = 0; i < records.length; i++) { var employee = records[i]; employee.EmployeeName = employee.FirstName + " " + employee.LastName; employee.EmployeeID = employee.uid; data.push(employee); } return data; } }); // prepare the data var ordersSource = { dataType: "xml", dataFields: [ // name - determines the field's name. // value - the field's value in the data source. // values - specifies the field's values. // values.source - specifies the foreign source. The expected value is an array. // values.value - specifies the field's name in the foreign source. // values.name - specifies the field's value in the foreign source. // When the ordersAdapter is loaded, each record will have a field called "EmployeeName". The "EmployeeName" for each record comes from the employeesAdapter where the record's "EmployeeID" from orders.xml matches to the "EmployeeID" from employees.xml. { name: 'EmployeeName', value: 'EmployeeID', values: { source: employeesAdapter.records, value: 'EmployeeID', name: 'EmployeeName' } }, { name: 'EmployeeID', map: 'm\\:properties>d\\:EmployeeID' },
 { name: 'ShippedDate', map: 'm\\:properties>d\\:ShippedDate', type: 'date' },
 { name: 'Freight', map: 'm\\:properties>d\\:Freight', type: 'float' },
 { name: 'ShipName', map: 'm\\:properties>d\\:ShipName' },
 { name: 'ShipAddress', map: 'm\\:properties>d\\:ShipAddress' },
 { name: 'ShipCity', map: 'm\\:properties>d\\:ShipCity' },
 { name: 'ShipCountry', map: 'm\\:properties>d\\:ShipCountry' }
 ],
 root: "entry",
 record: "content",
 id: 'm\\:properties>d\\:OrderID',
 url: "../sampledata/orders.xml",
 pager: function (pagenum, pagesize, oldpagenum) {
 // callback called when a page or page size is changed.
 }
};
var ordersAdapter = new $.jqx.dataAdapter(ordersSource);

pageNum - determines the initial page number when paging is enabled.
pageSize - determines the page size when paging is enabled.
pager - callback function called when the current page or page size is changed.
```
pager: function (pagenum, pagesize, oldpagenum) {
}
```
sortColumn - determines the initial sort column. The expected value is a data field name.
sortDirection - determines the sort order. The expected value is 'asc' for (A to Z) sorting or 'desc' for (Z to A) sorting.
sort - callback function called when the sort column or sort order is changed.
```
sort: function (column, direction) {
}
```
filter - callback function called when a filter is applied or removed.
```
filter: function(filters, recordsArray)
{
}
```

addRow - callback function, called when a new row is/are added. If multiple rows are added, the rowid and rowdata parameters are arrays of row ids and rows.


addRow: function (rowId, rowData, position, commit) {
 // synchronize with the server - send insert command
 // call commit with parameter true if the synchronization with the server is successful 
 //and with parameter false if the synchronization failed.
 commit(true);
}

deleteRow - callback function, called when a row is deleted. If multiple rows are deleted, the rowid parameter is an array of row ids.


deleteRow: function (rowId, commit) {
 // synchronize with the server - send delete command
 // call commit with parameter true if the synchronization with the server is successful 
 //and with parameter false if the synchronization failed.
 commit(true);
}

updateRow - callback function, called when a row is updated. If multiple rows are added, the rowid and rowdata parameters are arrays of row ids and rows.


updateRow: function (rowId, newRowData, commit) {
 // synchronize with the server - send update command
 // call commit with parameter true if the synchronization with the server is successful 
 // and with parameter false if the synchronization failed.
 commit(true);
}

formatData - Before the data is sent to the server, you can fully override it by using the formatData function of the source object. The result that the formatData function returns would be sent to the server.

var source ={ datatype: "jsonp", datafields: [ { name: 'countryName' }, { name: 'name' }, { name: 'population', type: 'float' }, { name: 'continentCode' } ], url: "http://ws.geonames.org/searchJSON", data: { featureClass: "P", style: "full", maxRows: 50 }, formatdata: function (data) { return "my data"; }};

contentType: Use this option, If you want to explicitly pass in a content-type. Default is "application/x-www-form-urlencoded".

The example code below illustrates how to create jqxDataAdapter from a source object.

var dataAdapter = new $.jqx.dataAdapter(source, { loadComplete: function (data)  { // data is loaded.  }, loadError: function (xhr, status, error) {  // data is not loaded. } });

If you bind the DataTable to a remote data source using asynchronous requests(that is by default when you set the source object's url member and you did not set the async field to false), then make sure that you call any method or set a property once the data is loaded. To ensure that you call your code when the DataTable is loaded with data, you can use the DataTable's ready callback function or bind to the bindingComplete event before the DataTable's initialization and add your code inside the event handler.

Example

The sample code below shows how to use the ready callback function:


var url = "../sampledata/beverages.txt";
// prepare the data
var source =
{
 dataType: "json",
 dataFields: [
 { name: 'name', type: 'string' },
 { name: 'type', type: 'string' },
 { name: 'calories', type: 'int' },
 { name: 'totalfat', type: 'string' },
 { name: 'protein', type: 'string' }
 ],
 id: 'id',
 url: url
};
var dataAdapter = new $.jqx.dataAdapter(source);
$("#dataTable").jqxDataTable(
{
 width: 400,
 source: dataAdapter,
 ready: function () {
 $("#dataTable").jqxDataTable('hideColumn', 'name');
 },
 columns: [
 { text: 'Name', dataField: 'name', width: 250 },
 { text: 'Beverage Type', dataField: 'type', width: 250 },
 { text: 'Calories', dataField: 'calories', width: 180 },
 { text: 'Total Fat', dataField: 'totalfat', width: 120 },
 { text: 'Protein', dataField: 'protein', width: 100}
 ]
});

Example

The sample code below shows how to use the bindingComplete event:


var url = "../sampledata/beverages.txt";
// prepare the data
var source =
{
 dataType: "json",
 dataFields: [
 { name: 'name', type: 'string' },
 { name: 'type', type: 'string' },
 { name: 'calories', type: 'int' },
 { name: 'totalfat', type: 'string' },
 { name: 'protein', type: 'string' }
 ],
 id: 'id',
 url: url
};
var dataAdapter = new $.jqx.dataAdapter(source);
$("#dataTable").on('bindingComplete', function(event)
{
 $("#dataTable").jqxDataTable('hideColumn', 'name');
});
$("#dataTable").jqxDataTable(
{
 width: 400,
 source: dataAdapter,
 columns: [
 { text: 'Name', dataField: 'name', width: 250 },
 { text: 'Beverage Type', dataField: 'type', width: 250 },
 { text: 'Calories', dataField: 'calories', width: 180 },
 { text: 'Total Fat', dataField: 'totalfat', width: 120 },
 { text: 'Protein', dataField: 'protein', width: 100}
 ]
});