Mark Wilson I am the creator of TopXML. I am available for international and local (Australia) contracts. I am a Solution Architect/Business Analyst. I have worked in IT in several countries (NZ, Australia, South Africa, UK) building and training teams for government and very large non-governmental organizations. I am ex-Microsoft Consulting Services. I wrote the first book on Microsoft XML published in 2000 called XML Programming with VB and ASP. Most recently I have been building tools for the SEO industry. Ask me for a 37 point SEO health-checkup for your website.
First posted :
09/04/2000
Times viewed :
962
ADO, XML and XSLT - Populating Tables and Combo boxes
In the following article we are going to discuss how to dynamically create a
drop-down list and a table from a ADO recordset using a generic
XSL stylesheet. We are then going to discuss how to add metadata to our XML, so that
we can show specific information into our dynamically created table.
This article is using the original stylesheet transformation, xmlns:xsl="http://www.w3.org/TR/WD-xsl",
therefore with client side processing, it can be used with any version of IE5.
ADO exports a recordset in an XML format through the .save adPersistXML method.
This method persists a recordset to a file or ( from version 2.5 ) to a
Stream in an XML format. This feature is very useful for sending data to a
client from a web server and can become really interesting if coupled with
XSLT. Through this technology we can transform our XML data into HTML, separating the presentation from the
data and making a step further towards reusability. This article explores the
characteristics of the ADO recordset XML document and shows some sample uses of
this technology through XSLT.
For this article ADO 2.5 is used, while the
XML parser
is MSXML 2.0. This parser has the first and incomplete implementation of
XSLT (at that time still called XSL ), but
is the default that ships with Internet Explorer 5.0, which is the browser target of this
document. The explanations assumes that the reader has basic knowledge
of ADO and databases and familiarity with XSL(T), in particular with the context()
operator. The context() operator provides a method for
evaluating the position of a node in the context of a query. It's
similar to the XSLT position() function found in the XSLT specification
Figure 1
sample of the structure of the elements of an xml recordset.
Let's start off with the structure of the XML returned from an ADO
recordset. The <xml> element
is the root of the document; it has two children: <s:Schema>
and <rs:data>. The first holds the meta-data describing the
structure of the recordset while the second holds the actual data. To
view how this XML is returned, have a look at these four samples of XML recordsets taken from the Northwind
database, namely: categories, products,
orders and employees.
Schema
Inside the schema of the recordset, each field is represented by an
element <s:AttributeType> for example:
- The value of the name attribute is the name of the field;
Other
attributes represent various properties of the field, two of the key
being:
- rs:basetable
contains the name of the table to which the field belongs;
- rs:keycolumn
= "true" signals if the field is a primary key of the
table,
The <s:AttributeType> elements also
has a child element <s:datatype> which holds information about
its data type (@dt:type) such as its maximum length (@dt:maxLength).
Some sample XPath queries on the schema:
XPath is the query language used to find data in the XML. XSLT enables
us to transform our XML. XPath is a subset of XSLT, although it can also
stand on its own. Here are a few examples of how to use XPath:
Find the data type of the first field of the
recordset:
The second child of <xml> is <rs:data> which has a set of
<z:row> children each representing one record of the recordset. The actual
data is held inside the attributes of <z:row> element, where every
attribute's name is the same as the value of the attribute's defined in the
corresponding Schema <s:AttributeType> element:
Note: If the value of a field in a record is null, the attribute on the z:row is not
created.
Update, Delete, Insert
Inside a recordset we can also store updates, insertions and deletions.
This allows one to set these elements in our XML. When the XML recordset
is saved to the database, these operations will occur.
A deleted row is represented by wrapping the <z:row> representing the
record to be deleted inside a <rs:delete> element:
The first two cases present no particular problems, but the updated record
has some peculiarities: <rs:original> holds the original record, the one
retrieved from the database. Holding a reference to the original record is the
only way through which ADO can then check if the record still exists or if it
has been modified when time comes to merge the data back to the database. The
other <z:row> holds the modifications, in our example the new FirstName="Mark". If one or more values had to be forced to
null, basically where we delete a value from a row, we should
include their field names (space separated) in the value of the attribute @rs:forcenull.
In the following example of an updated row in, we force the FirstName and Title fields to
be null:
This approach is not well suited for XSL transformations and interpretations
because some string-manipulation script is needed in order to extract
information from this attribute.
A simple strategy for building a combo box would be to iterate through the rows
and insert the values of the fields we are interested in (the key and the
display value, in this case CategoryID and categoryname) in an HTML
<SELECT> element.
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"
xmlns:html="http://www.w3.org/tr/rec-html40" result-ns="">
<xsl:template match="/">
<HTML>
<BODY>
<SELECT>
<xsl:apply-templates select="xml/rs:data/z:row" order-by="@*[1]" />
</SELECT>
</BODY>
</HTML>
</xsl:template>
<xsl:template match="z:row">
<OPTION>
<xsl:attribute name=
"value">
<xsl:value-of select=
"@*[nodeName()=/xml/s:Schema/s:ElementType/s:AttributeType
[@rs:keycolumn='true']/@name]" />
</xsl:attribute>
<!-- iterate through each 'row' attribute in the Schema definition -->
<xsl:for-each select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
<!-- construct the text for each 'option' value,
only if it is not a keycolumn -->
<xsl:choose>
<xsl:when test="@rs:keycolumn[text()='true']"></xsl:when>
<xsl:otherwise>
<xsl:value-of select="context(-2)/@*[nodeName()=context(-1)/@name]" />
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</OPTION>
</xsl:template>
</xsl:stylesheet>
See Results
This stylesheet is pretty simple: the template that is applied for each row
(<xsl:template match="z:row">) extracts the values of CategoryID and categoryname and copies the CategoryID to the
attribute "value" and the categoryname to the text of the
<OPTION> element.
A generic combo box stylesheet
We can produce a generic
stylesheet having the values used to fill the "value"
attribute of the <OPTION> element to be dependant on which field is the
key for the recordset. One implementation is:
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"
xmlns:html="http://www.w3.org/tr/rec-html40" result-ns="">
<xsl:template match="/">
<HTML>
<BODY>
<SELECT>
<xsl:apply-templates select="xml/rs:data/z:row"
order-by="@*[1]" />
</SELECT>
</BODY>
</HTML>
</xsl:template>
<xsl:template match="z:row">
<OPTION>
<xsl:attribute name="value">
<xsl:value-of select=
"@*[nodeName()=/xml/s:Schema/s:ElementType/s:AttributeType
[@rs:keycolumn='true']/@name]" />
</xsl:attribute>
<!-- iterate through each 'row' attribute in the Schema definition -->
<xsl:for-each
select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
<!-- construct the text for each 'option' value,
only if it is not a keycolumn -->
<xsl:choose>
<xsl:when test="@rs:keycolumn[text()='true']">
</xsl:when>
<xsl:otherwise>
<xsl:value-of
select="context(-2)/@* [nodeName()=context(-1)/@name]" />
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</OPTION>
</xsl:template>
</xsl:stylesheet>
</xml>
See Results
This is a more complicated stylesheet: like the previous example, it iterates through all
the rows but, in the template matching <z:row>, instead of accessing
directly the attribute "CategoryID" by hard coding the field name in
the stylesheet, it chooses the attribute that has the nodeName property
equal to the name of the first key field and copies it into the value attribute of
the <OPTION> element, in the following syntax:
Note: This works fine if there is only a key field in the recordset, which usually
occurs with lookup table which has no pending changes in the recordset, which is
also a normal feature of a read-only lookup table. Then, to construct the text
for each option, the template iterates through all the fields and chooses those
that are not keys:
Building a table from an XML document exported from ADO can be achieved in
various ways, depending on what the requirements are. For a simple table without
formatting we could use the following stylesheet:
This stylesheet goes through the <s:AttributeType> elements and
retrieves the name of the fields to construct the table headers. What we notice
here is that the [EmployeeID] field is also shown in the table. This field has
not been required explicitly in the SQL query but ADO retrieves it anyway
because it is the primary key. ADO signals it by setting @rs:hidden = 'true' on
<s:AttributeType> .
Then the z:row template is applied to all the rows. The z:row template
iterates through all the <s:AttributeType> elements in order to retrieve
the value of the correspondent attribute of the z:row element: this value is
used to construct one table cell.
Why do we have to iterate through the
<s:AttributeType> elements instead of directly accessing the attributes of
the <z:row>? As mentioned previously, if the value of a
field in the database is null then the attribute on the z:row is not created. Because of missing attributes,
problems can arise if we simply iterate through
all the attributes. Therefore, iterating through the Schema attributes
ensures that
we are accessing all the fields we want to output. This is an example of
what happens when you iterate through the actual Data defined attributes:
The above syntax will create a correct table based on the same data:
Dr.
John
Frez
Paul
Sert
Dr.
Anne
Ler
Hide 'hidden' fields
In order to hide 'hidden' fields when iterating through all the Schema's <s:AttributeType>
attributes, we should skip those marked with @rs:hidden = 'true'.
The new stylesheet
becomes :
We are still far from a usable table; we should at least provide a way to show
decent captions and a little formatting. In order to do so we need to add some
information to the recordset XML document. One of the ways to accomplish adding this
extra metadata, is we can store the metadata in an other
document and add it to the recordset document before the transformation on
the client or add it before sending the document to the client (More in the note ). This
is an example of how the metadata can be organized inside this new XML document:
<layout>
<field name="TitleOfCourtesy" caption="T.o.C" info="Title of courtesy"/>
<field name="FirstName" caption="First Name" info="First name of the employee"/>
<field name="LastName" caption="Last Name" info="Last name of the employee"/>
</layout>
Attribute name
Description
name
caption
info
format
visible = 'false'
: name of the field
: caption of the header
: tooltip of the header
: format of the field
: hide the column of this field in the table
Image 2: The structure of
one
document with the added
layout element.
Captions and tooltips
Here follows the new stylesheet that uses the
information from the <layout> element and its children to build captions
and tooltips :
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"
xmlns:html="http://www.w3.org/tr/rec-html40" result-ns="">
<xsl:template match="/">
<TABLE border='1'>
<THEAD>
<xsl:apply-templates
select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType" />
</THEAD>
<TBODY>
<xsl:apply-templates select="/xml/rs:data/z:row"
order-by="@*[2]" />
</TBODY>
</TABLE>
</xsl:template>
<!-- The "s:AttributeType" attributes allows us to find the related
caption and tooltip to output to the table header -->
<xsl:template match="s:AttributeType">
<xsl:choose>
<!-- ignore all hidden attritutes -->
<xsl:when test="@rs:hidden" />
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-1)/@name]/@visible[text() $ieq$ 'false']"/>
<xsl:otherwise>
<th>
<xsl:if
test="/xml/layout/field[@name $ieq$ context(-1)/@name]/@info">
<xsl:attribute name="title">
<xsl:value-of
select="/xml/layout/field[@name $ieq$ context(-1)/@name]/@info" />
</xsl:attribute>
</xsl:if>
<xsl:choose>
<xsl:when
test="/xml/layout/field[@name $ieq$ context(-1)/@name]/@caption">
<xsl:value-of
select="/xml/layout/field[@name $ieq$ context(-1)/@name]/@caption" />
</xsl:when>
<!-- If no caption, select the name of the field from the schema -->
<xsl:otherwise>
<xsl:value-of select="@name" />
</xsl:otherwise>
</xsl:choose>
</th>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<!-- As we find to a new row, iterate through
the Schema's row child attributes to output the value of the row -->
<xsl:template match="z:row">
<TR>
<xsl:for-each
select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
<xsl:choose>
<!-- ignore all hidden attributes -->
<xsl:when test="@rs:hidden" />
<xsl:when test=
"/xml/layout/field [@name $ieq$ context(-1)/@name]/@visible[text()$ieq$'false']"/>
<xsl:otherwise>
<td nowrap='true'>
<xsl:value-of
select="context(-2)/@*[nodeName()=context(-1)/@name]" />
</td>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</TR>
</xsl:template>
</xsl:stylesheet>
See Result
Formatting
The result has improved, but if we look at a more heterogeneous recordset
like orders and the HTML
table result of its transformation, there is still something missing.
The date and currency fields, for example, should be formatted and perhaps
we could also right align all fields whose data type is not a string. This is an
example of using Scripting in an XSL file. The
new stylesheet will look as follows:
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"
xmlns:html="http://www.w3.org/tr/rec-html40" result-ns="">
<xsl:template match="/">
<TABLE border='1'>
<!-- apply the headers -->
<THEAD>
<xsl:apply-templates
select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType" />
</THEAD>
<!-- insert the data -->
<TBODY>
<xsl:apply-templates select="/xml/rs:data/z:row"
order-by="@*[2]" />
</TBODY>
</TABLE>
</xsl:template>
<xsl:template match="s:AttributeType">
<xsl:choose>
<xsl:when test="@rs:hidden" />
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-1)/@name]/@visible[text() $ieq$ 'false']"/>
<xsl:otherwise>
<th>
<xsl:if
test="/xml/layout/field[@name $ieq$ context(-1)/@name]/@info">
<xsl:attribute name="title">
<!-- Output the tooltip information -->
<xsl:value-of
select="/xml/layout/field [@name $ieq$ context(-1)/@name]/@info" />
</xsl:attribute>
</xsl:if>
<xsl:choose>
<!-- If there is a related caption, insert this value or just
insert the name of the current row -->
<xsl:when
test="/xml/layout/field[@name $ieq$ context(-1)/@name]/@caption">
<xsl:value-of
select="/xml/layout/field [@name $ieq$ context(-1)/@name]/@caption" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="@name" />
</xsl:otherwise>
</xsl:choose>
</th>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="z:row">
<tr>
<xsl:for-each
select="/xml/s:Schema/s:ElementType[@name='row']/s:AttributeType">
<xsl:choose>
<xsl:when test="@rs:hidden" />
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-1)/@name]/@visible[text() $ieq$ 'false']" />
<xsl:otherwise>
<td nowrap='true'>
<!--If the data type of the field is not 'string',
the text is aligned on the left.-->
<xsl:choose>
<xsl:when
test="s:datatype/@dt:type[text() = 'string']" />
<xsl:otherwise>
<xsl:attribute name="align">right</xsl:attribute>
</xsl:otherwise>
</xsl:choose>
<xsl:apply-templates
select="context(-2)/@*[nodeName()=context(-1)/@name]" />
</td>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</tr>
</xsl:template>
<!-- whenever an element or attribute is found ("@*"),
it will run through this template -->
<xsl:template match="@*">
<!-- depending on the format returned for the current item,
based on its node name, run its related Script function -->
<xsl:choose>
<!-- currency value -->
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-2)/@name]/@format[text() $ieq$ 'currency']">
<xsl:eval>getCurrency(this.text)</xsl:eval>
</xsl:when>
<!-- datetime value -->
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-2)/@name]/@format[text() $ieq$ 'dateTime']">
<xsl:eval>getDateTime(this.text)</xsl:eval>
</xsl:when>
<!-- date value -->
<xsl:when test=
"/xml/layout/field[@name $ieq$ context(-2)/@name]/@format[text() $ieq$ 'date']">
<xsl:eval>getDate(this.text)</xsl:eval>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="text()" />
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:script>function getDateTime(value){ temp = new Date();
temp.setYear(value.substr(0,4));
temp.setMonth(value.substr(5,2));
temp.setDate(value.substr(8,2));
temp.setHours(value.substr(11,2));
temp.setMinutes(value.substr(14,2));
temp.setSeconds(value.substr(17,2)); return
temp.toLocaleString(); } function getCurrency(value){return
formatNumber(value, "#,###.00 $");} function getDate(value){ temp
= new Date(); temp.setYear(value.substr(0,4));
temp.setMonth(value.substr(5,2));
temp.setDate(value.substr(8,2)); return
temp.toLocaleString().substr(0,10); }</xsl:script>
</xsl:stylesheet>
Inside the <layout> element we define the layout for the Products table:
<layout>
.....
<field name="UnitPrice"
format="currency"
caption="Unit Price" info="This is the unit price of the product" />
.....
</layout>
See Result
This stylesheet introduces a new template for the attribute, that is called
by the template of the <z:row>. This template looks in the format property
of the field inside the <layout> and selects how and if to format the
value of the attribute through some xsl:script. We could also determine the
datatype from the schema without having to specify it in the layout element. A
currency field is marked setting s:datatype/@rs:dbtype = 'currency' and a
dateTime value is marked with s:datatype/@dt:type = 'dateTime'. These two
conditions can be added in the @* template ( here is the last stylesheet
) :
For a last improvement let's introduce the sorting functionality, where the user
can click on a header of the table to sort the row. I will change
the way we render the document in the browser, not using the
<?xml-stylesheet type="text/xsl" href="myStyle.xsl" ?>
processing instruction anymore but through script, using the tranformNode()
method of the XMLDomDocument. Here is a simple page
that shows it:
<html>
<head>
<title>ADO Sortable table</title>
<script>
function init(){
return display();
}
function display(){
tableDiv.innerHTML = xmlData.XMLDocument.transformNode _
(xmlXSL.XMLDocument.documentElement);
var headers = tableDiv.children(0).children(0).children(0).children
for (var x=0; x<headers.length; x++)
headers(x).onclick = sort;
}
function sort(){
var objSortNode = xmlXSL.XMLDocument.selectSingleNode("//@order-by")
var strOldValue = objSortNode.nodeValue
var objAttrTypeNode = xmlData.XMLDocument.selectSingleNode _
("/xml/s:Schema/s:ElementType/s:AttributeType[" + (this.rsNumber-1) + "]")
var strFieldName = objAttrTypeNode.getAttribute("name")
var fieldDT = objAttrTypeNode.selectSingleNode("s:datatype/@dt:type").text
if (fieldDT == 'string' || fieldDT == 'boolean') {pre = ""; post = ""}
else {pre = "number("; post = ")"}
if (strOldValue.indexOf(strFieldName)!=-1){
if (strOldValue.substr(0,1) == "-"){
strDirection = "+";
}
else{
strDirection = "-";
}
}
else{
strDirection = "-";
}
var strQuery = strDirection + " " + pre + "@" + strFieldName + post;
objSortNode.value = strQuery;
display();
}
</script>
</head>
<body onload="init()">
<xml async=false id=xmlData src="productsWithXSLTableExt3.xml"></xml>
<xml async=false id=xmlXSL src="TableExt3.xsl"></xml>
<div id=tableDiv></div>
</body>
</html>
The page loads the two xml document, data and xsl, then it inserts the result
of the transformation into the innerHTML property of a DIV.
To create sorting functionalities we will leave the stylesheet as it is ( *
except for a little addition ) and we manipulate the table through the DOM to
attach an event to the click event of the headers. Why should we choose this
method instead of hard-coding directly the onclick event in the xsl ? Because in
this way we can use the same stylesheet for different purposes: we don't have it
relying on other documents like the sorting code called by the onclick event
does.
The sort routine works finding the only @order-by attribute inside the
stylesheet ( that is in xml format and can then be manipulated through the xml
Dom ) and changing its value to something like : "+@Description" or
"-number(Freight)", depending on the data type. The needed information
is retrieved from the <AttributeType> element that has the index equal to
the rsNumber attribute of the HTML <TH> element..
* We copy the value of the @rs:number attribute of the <AttributeType>
element into the rsNumber attribute of the <TH> HTML element.
In all the preceding examples the xml document is provided with the
<layout> element already added. This simulates the situation in which we
add the <layout> element on the server, typically in an ASP page.
Server side strategy
With this sub we can write the xml document exported by an ADO Recordset to
the Response object, adding the <layout> element, that is contained in the
file indicated by the parameter pstrLayoutFile.
Sub recordsetToResponse(pobjRecordset, pstrLayoutFile)
Set lobjXMLData = Server.CreateObject("Microsoft.XMLDOM")
Set lobjXMLLayout = Server.CreateObject("Microsoft.XMLDOM")
lobjXMLData.async = false
lobjXMLLayout.async = false
lobjXMLLayout.load Server.mapPath(pstrLayoutFile)
pobjRecordset.save lobjXMLData, adPersistXML
lobjXMLData.documentElement.appendChild(lobjXMLLayout.documentElement)
Response.ContentType = "text/xml"
Response.Write("<?xml version='1.0' encoding='windows-1252' ?>")
Response.Write lobjXMLData.xml
End Sub
This strategy has one main disadvantage: first we have to load the recordset
into an XMLDocument object and then we add the node. If this work could be moved
on the client we could save some processing time.
Client side strategy
We add another XML Document to the page: xmlLayout and we add its root
element to the XML document of the recordset.
Michele Vivoda, born in Italy in 1971, works is currently employed by
A.S.C., a consulting company based in Amsterdam. He is specialized in XML (of
course) and WEB tecnologies.
You can reach him at MicheleVivoda@hotmail.com
