How to convert an XML ADO Client-Side recordset into a sortable HTML Table with the W3C DOM<

How to convert an XML ADO Client-Side recordset into a
sortable HTML Table with the W3C DOM.

By Peter A. Bromberg, Ph.D.

I guess the subtitle of this piece should be "DOM DOM DOM , DOM, DOM de doobie DOM..." because it's all about Document Object Model - getting access to a persisted recordset's rows through the DOM, creating a table out of these rows using the DOM, and then showing how to sort the table by chosen columns again, using intrinsic methods of the HTML DOM.

I like the DOM - a lot of the HTML DOM methods are the same or similar to the ones I've become familiar with from studying the XML DOM. And you can do a lot of neat dynamic HTML stuff with DOM methods - often with a lot less complexity than using Microsoft's semi-proprietary DHTML document object model. So let's get started on our little exercise.

First, we are going to make up a little "Server" asp page to accept a parameter on the querystring, do a database lookup from the familiar Northwind sample database on SQL Server 2000, and return the ADO recordset as XML by saving it directly to the ASP Response Object:

<%@Language="JScript" %>
<%
var srchterm = Request.Querystring("companyname")
Response.ContentType = "text/xml";
var adOpenStatic = 3,adLockReadOnly = 1,adPersistXML = 1, adCmdText = 0x0001;
var sql = "SELECT * FROM customers "
if (srchterm !="")
sql+= " WheRE CompanyName Like '%" +srchterm + "%' order by CompanyName";
var Conn = Server.CreateObject("ADODB.Connection");
Conn.Open("Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=northwind;Data Source=(local)");
var rstObj = Server.CreateObject("ADODB.Recordset");
try {
rstObj.Open(sql,Conn,adOpenStatic,adLockReadOnly,adCmdText);
if (rstObj.RecordCount > 0) {
rstObj.Save(Response,adPersistXML);
} else {
Response.Write("<root><b>No Records Found</b></root>");
}
}
catch (e) {
Response.Write("<root><b>Error: " + e.description + "</b></root>");
}
%>

To summarize the code above, we:

1. Check the querystring for our search term ("var srchterm = Request.Querystring("companyname")
2. Set our Response.ContentType header to "text/xml" so the receiving page can load the stream directly into and XML DOMDocument.
3. Set a couple of needed ADO constants for recordset, cursor and command types.
4. Create our SQL Statement, adding the "Where" clause only if the Querystring is not empty.
5. Create our ADO OLEDB Connection to our SQL Server and open the connection.
6. retrieve our recordset and SAVE it as an XML stream right into the ASP Response object.

Note that the actual database call is wrapped in a try-catch block to enable structured exception handling without our page "blowing up" if something goes wrong. So now we've actually got a customrecordset streaming over the wire to the calling page. You can take the above code and save it as "GetXML.asp" on your server.

Now, let's look at what happens on the client side:

<html>
<head>
<script language=VBScript>
dim oTable
dim i, xmlNode
dim oParent, oElement
Dim oRow, oCell, tmp
sub createTable()
on error resume next
set oTable=document.createElement("<TABLE id='datatable' style='font-family:Verdana;'></TABLE>")
document.body.appendChild(oTable)
set oTable =datatable
set oRow=oTable.insertRow()
oRow.style.backgroundcolor="gray"
oRow.style.color="white"
Set xmlDoc = CreateObject("MSXML2.DomDocument.3.0")
xmlDoc.async=false
xmlDoc.load("getXML.asp?CompanyName=" &srchterm.value)
set xmlNode = xmlDoc.getElementsByTagName("z:row")
for i = 0 to xmlNode(0).Attributes.length -1
set oCell=oRow.insertCell()
oCell.innerText =xmlNode(0).Attributes(i).name
next
for i = 0 to xmlNode.length-1
set oRow = datatable.insertRow()
oRow.style.color="blue"
oRow.style.backgroundColor="lightgrey"
for j = 0 to xmlNode(0).Attributes.length-1
set oCell=oRow.insertCell()
tmp=xmlNode(i).Attributes(j).text
if tmp <> "" then oCell.innerText=tmp
next
next
end sub
</script>

Here we have a function createTable() that is actually called from a button click that I'll show you in a minute. It starts by creating the Table element. You can pass "TABLE" or any other TAG name to the createElement method, or you can also pass a vaild string of HTML, including the closing tag if it's a block element, and this is what I've done here. We then add the Table to the document with document.body.appendChild(oTable). This is required, or we won't get access to our element with the DOM.

We then begin adding the heading columns and the rows. We add the first row with "Set oRow=oTable.insertRow()". Now it's time to get our recordset, so we create an MSXML2.DomDocument.3.0 and call its load method with the URL to our GetXML.asp page?CompanyName=" & srchterm.value. If you guessed that srchterm.value is the value of an input element in the HTML part of the page, you are correct. If you remember from looking at the construction of the SQL in GetXML.asp, this can be a name or just any part of a name.

The easy way to gain access to a persisted ADO recordset in an XML DOMDocument object is to use the getElementsByTagName("z:row") method. This returns an IXMLNodeList that's easy to iterate through, and each of the node items contains a row in our recordset, with the name and text properties of all the attributes (columns) easily accessible. So you can see that we are iterating through the Attributes collection, inserting a cell in our table for each column heading, and populating it's innerText property with the value of the xmlNode(0).Attriibutes(i).name .

Creating the rows and cells for the body of the table is almost exactly the same, except we populate the cells' innerText properties with value of each node's (row's) attribute instead of the attribute's name. And DOM de DOM! You DOM got a table!

Now let's switch over to Javascript for our insertion sort method:

if ( ( (!fReverse && textRowInsert <= textRowCurrent)
|| ( fReverse && textRowInsert >= textRowCurrent) )
&& (iRowInsert != iRowWalk) )
{
eRowInsert = t.children[iRowInsert];
eRowWalk = t.children[iRowWalk];
t.insertBefore(eRowInsert, eRowWalk);
iRowWalk = iRowInsert; // done
}
}
}
}
</script>

This one's a little tricky, but if you follow the code it becomes clear:
First, let's look at the code for the listbox that calls this function --

<INPUT ID=srchterm TYPE=TEXT>Search Term<BR>
<INPUT TYPE=button value ="Search!" onClick="VBSCRIPT:createTable()"> 
Sort table by:<select name=ColSort onchange="Javascript:insertionSort(datatable.children[0], 1, datatable.rows.length - 1, false, ColSort.value);">
<Option value=0>Customer ID</option>
<Option value=1>Company Name</option>
<Option value=2>Contact Name</option>
<Option value=3>Contact Title</option>
<Option value=4>Address</option>
<Option value=5>City</option>
<Option value=6>Postal Code</option>
<Option value=7>Country</option>
<Option value=8>Phone</option>
<Option value=9>Fax</option>
</select>

You can see that when the onChange event is fired, the code calls the insertionSort method with the rows collection of the table, then the starting row at row 1 (we don't want to sort the header row!), then the ending row, then a boolean for whether we want a reverse sort, and finally the column number to sort on which is the selected option value from our listbox. Going back to the function, we are going to walk the rows,comparing the text value of the sort column to the one in the following row, and if it's greater than (string comparison), we insertBefore. By repeating this iteration through all the rows, we have effectively completed sorting our table rows.

I hope this little exercise give you some ideas to help solve your own challenges using the DOM. After all, who wants to sit around typing endless <TR>, <TD>, </TD> and </TR> tags? There's some excellent documentation on HTML DOM methods at MSDN online.

download the code that accompanies this article

Peter Bromberg is an independent consultant specializing in distributed .NET solutionsa Senior Programmer /Analyst at in Orlando and a co-developer of the NullSkull.com developer website. He can be reached at info@eggheadcafe.com