N342 ASP ADO XML XSL |
Modified: |
Mixing ASP JavaScript, HTML, SQL, etc. in ASP scripts produces hard to write and read scripts that entangle the logic with the user interface. We have also seen that XML data can be transformed by XSL to generate HTML, very useful in separating representation data from its presentation.
Our goal is to separate the program logic from the user interface, allowing programmers and user interface designers to work somewhat independently. With data maintained mostly in databases, we will develop patterns for transforming the database tables into the dynamic part of the user interface. Our end result will be an ASP program to interact with the user and access data bases and XSL to present the user interface and data.
First an example of ASP and databases that combines the logic and user
interface then a review of database tables, XML and XSL.
Let's examine an Access database table first. The Portfolio table at right has 3 traders, 6 records, each with 4 fields: STOCKID, ID, SYMBOL and SHARES. Generally our ASP programs will be reading and updating the tables but, of course, we can use the Access application to do the same operations.
Exercise 0 - Access
|
We will use the Portfolio table from the Stock Trading project as an example case.
To display the table contents in HTML for a browser we will need to:
Portfolio.ASP <%@ Language=JScript%>
<%
var conn = Server.CreateObject("ADODB.Connection");
conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" +
Server.MapPath("Project.mdb"));
var rs = conn.Execute("SELECT * FROM Portfolio;");
Response.Write(
"<h1>Exercise 0.5</h1>" +
"<table border='1'><tr>" +
" <td>ID</td><td>Symbol</td></tr>");
while (!rs.EOF) {
Response.Write(
"<tr><td>" + rs("ID")+ "</td>" +
"<td>" + rs("SYMBOL") + "</td></tr>");
rs.MoveNext();
}
Response.Write("</table>");
conn.Close();
%>
|
Table and partial HTML produced
|
Although a table is produced, the weakness of this solution is that program logic and generation of the user interface (e.g. the table) are combined. Program logic requires a programmer; the user interface requires the user interface designer to design and test the interface; and a programmer must make any changes to the code.
For greater separation of the programming logic and the user interface, the programmer implementing the program logic and the interface designer implementing the interface need to be able to work only on their separate parts of the system.
Exercise 0.5 - ASP
|
We will again use the Portfolio table from the Stock Trading project as an example case. The table with three traders appears at right.
The Portfolio database table can be represented as the XML structure below:
Portfolio.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl"
href="PortfolioContent.htm"?>
<Portfolio>
<STOCKID>1
<ID>Ray</ID><SYMBOL>IBM</SYMBOL><SHARES>10</SHARES>
</STOCKID>
<STOCKID>2
<ID>Ray</ID><SYMBOL>MSFT</SYMBOL><SHARES>100</SHARES>
</STOCKID>
<STOCKID>3
<ID>Harold</ID><SYMBOL>FORD</SYMBOL><SHARES>15</SHARES>
</STOCKID>
<STOCKID>4
<ID>Robin</ID><SYMBOL>UAL</SYMBOL><SHARES>230</SHARES>
</STOCKID>
<STOCKID>5
<ID>Robin</ID><SYMBOL>FORD</SYMBOL><SHARES>100</SHARES>
</STOCKID>
</Portfolio>
|
We have seen how Portfolio.xml can be transformed using XSL to HTML for the browser. The following is the PortfolioContent.htm to perform the transformation, yielding the same result as Portfolio.ASP earlier.
PortfolioContent.htm
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/1999/xhtml">
<xsl:output method="html" />
<xsl:template name="PortfolioContent">
<h1>Exercise 1 and 2</h1>
<table border="1">
<tr>
<td>ID</td>
<td>Symbol</td>
</tr>
<xsl:for-each select="Portfolio/STOCKID">
<tr>
<td><xsl:value-of select="ID" /></td>
<td><xsl:value-of select="SYMBOL" /></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
<xsl:template match="/">
<xsl:call-template name="PortfolioContent" />
</xsl:template>
</xsl:stylesheet>
|
HTML output <table border="1" > <tr> <td>ID</td> <td>Symbol</td> </tr> <tr> <td>Ray</td> <td>IBM</td> </tr> <tr> <td>Ray</td> <td>MSFT</td> </tr> <tr> <td>Harold</td> <td>FORD</td> </tr> <tr> <td>Robin</td> <td>UAL</td> </tr> </table> |
Up to this point, we have used the IE browser to combine XML and XSL to display a HTML page. The approach is illustrated below where the XML file references the XSL file when loaded to display a table based upon the data structure defined in XML.
How it works
|
 |
Exercise 1 - XML XSL
|
Combining XML and XSL on the browser has several drawbacks:
Combining XML and XSL on the server running an ASP program rather than on the browser client has three key advantages:
The simplest use is to combine XML and XSL on the server in ASP to apply the
transformations and write out the result as HTML. The
following
ASP example:
Portfolio.asp
<%@ LANGUAGE = JScript %>
<%
// Load the XML
var xml = Server.CreateObject("MSXML2.DOMDocument");
xml.async = false;
xml.load(Server.MapPath("Portfolio.xml"));
// Load the XSL
var xsl = Server.CreateObject("MSXML2.DOMDocument");
xsl.async = false;
xsl.load(Server.MapPath("PortfolioContent.htm"));
Response.Write(xml.transformNode( xsl ));
%>
|
Exercise 2 - ASP XML XSL
|
XML can be dynamically generated dynamically from a database rather than loading static XML from a file. The following example:
Note that database table field names are transformed to XML attributes. For example, the first table row entry for fields ID and SYMBOL is XML:
<z:row ID="Ray" SYMBOL="IBM"/>.
Portfolio.ASP <%@ Language=JScript%>
<%
conn = Server.CreateObject("ADODB.Connection");
conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" +
Server.MapPath("Project.mdb"));
rs = conn.Execute("SELECT ID, SYMBOL FROM Portfolio");
var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
xmlDoc.async = false;
rs.Save (xmlDoc, 1) ;
// Convert rs recordset to XML tree
// Save as XML (not required)
xmlDoc.save(Server.MapPath("FromPortfolio.xml"))
var xsl = Server.CreateObject("Msxml2.DOMDocument");
xsl.async = false;
// Convert XML tree using XSL
xsl.load(Server.MapPath("PortfolioContent.htm"))
// Write tree as HTML
Response.Write( xmlDoc.transformNode( xsl ) );
conn.Close();
%>
Table and partial HTML produced
|
PortfolioContent.htm
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<xsl:output method="html" />
<xsl:template name="PortfolioContent">
<h1>Exercise 3</h1>
<table border="1" ID="Table1">
<tr>
<td>ID</td>
<td>Symbol</td>
</tr>
<xsl:for-each select="xml/rs:data/z:row">
<tr>
<td><xsl:value-of select="@ID" /></td>
<td><xsl:value-of select="@SYMBOL" /></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
<xsl:template match="/">
<xsl:call-template name="PortfolioContent" />
</xsl:template>
</xsl:stylesheet>
Partial XML produced by rs.Save (xmlDoc, 1); <xml> <rs:data> <z:row ID="Ray" SYMBOL="IBM"/> <z:row ID="Ray" SYMBOL="MSFT"/> <z:row ID="Harold" SYMBOL="FORD"/> <z:row ID="Robin" SYMBOL="UAL"/> <z:row ID="Robin" SYMBOL="FORD"/> </rs:data> </xml> |
Exercise 3 - ASP XML XSL Database
|
Combining or joining data from several database table sources is a fundamental operation of relational database systems. For the project, we will often need to combine data from two or more table through defined relationships.
Consider the data needed to produce a portfolio. The complete portfolio includes a list of stock symbols, number of shares and total value of each stock. The necessary data is held in the two tables: Portfolio and Stock.
The two tables share a common field, SYMBOL, that defines a many to one relation from Portfolio to Stock (i.e. there are many FORD symbols in Portfolio to one FORD symbol in Stock).
To produce trader Harold's portfolio requires data from both tables, ID='Harold', SHARES and SYMBOL from Portfolio; and PRICE for the corresponding SYMBOL from Stock table.
|
 |
Logically, we first need all the "Harold" Portfolio records and all the Stock records where the SYMBOL matches. In database terms, we need a new table from joining the tables when the Portfolio SYMBOL = Stock SYMBOL. The SQL statement giving all fields from records with SYMBOL matching in the Portfolio and Stock tables is:
Reducing the fields to those needed:
and produces the following new table of records. The INNER JOIN is also know more intuitively as the EQUI-JOIN.
Since we wanted Harold's portfolio, we need to keep only records where ID='Harold' by:
The figure at right illustrates that the Stock and Portfolio tables are input to the Portfolio program which outputs the results of applying PortfolioContent.
The ASP and XSL to produce Harold's portfolio is given below.
Notice that only the SELECT statement changed in the ASP from our previous example. Here we only need the SYMBOL and PRICE. The XSL file, PortfolioContent.htm must be changed to accommodate displaying the PRICE field.
Note also that the SELECT must specify both the table and field when the field is defined in both tables (e.g. Portfolio.SYMBOL).
Portfolio.ASP <%@ Language=JScript%>
<%
conn = Server.CreateObject("ADODB.Connection");
conn.Mode = 3;
conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" +
Server.MapPath("Project.mdb"));
rs = conn.Execute(
"SELECT PORTFOLIO.SYMBOL, PRICE " +
"FROM STOCK INNER JOIN PORTFOLIO ON " +
"STOCK.SYMBOL = PORTFOLIO.SYMBOL " +
"WHERE ID='Harold';");
var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
xmlDoc.async = false;
rs.Save (xmlDoc, 1) ; // Convert recordset to XML
// Save as XML
var xsl = Server.CreateObject("Msxml2.DOMDocument");
xsl.async = false; // Convert XML tree using XSL
xsl.load(Server.MapPath("PortfolioContent.htm"))
// Write tree as HTML
Response.Write(xmlDoc.transformNode(xsl));
conn.Close();
%>
Table and partial HTML produced
|
PortfolioContent.htm
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<xsl:output method="html" />
<xsl:template name="PortfolioContent">
<h1>Exercise 4</h1>
<table border="1">
<tr>
<td>Symbol</td>
<td>Price</td>
</tr>
<xsl:for-each select="xml/rs:data/z:row">
<tr>
<td><xsl:value-of select="@SYMBOL" /></td>
<td><xsl:value-of select="@PRICE" /></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
<xsl:template match="/">
<xsl:call-template name="PortfolioContent" />
</xsl:template>
</xsl:stylesheet>
Partial XML produced by rs.Save (xmlDoc, 1); <xml>
<rs:data>
<z:row SYMBOL="GMC" PRICE="38.1000"/>
<z:row SYMBOL="FORD" PRICE="15.6700"/>
</rs:data>
</xml>
|
Exercise 4 - ASP XML XSL Database JoinSQL
ASP/XSL
|
The portfolio still needs a total value of each stock requiring the computation: STOCKVALUE = SHARES * PRICE. The SQL statement is:
The ASP and XSL to produce Harold's portfolio is given below. Notice that only the SELECT statement changed in the ASP from our previous example.
Here we only need the SYMBOL, PRICE and computed STOCKVALUE.
The XSL file, PortfolioContent.htm must be changed to display the STOCKVALUE field.
Portfolio.ASP <%@ Language=JScript%>
<%
conn = Server.CreateObject("ADODB.Connection");
conn.Mode = 3;
conn.Open ("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" + Server.MapPath("Project.mdb"));
rs = conn.Execute(
"SELECT PORTFOLIO.SYMBOL, PRICE, " +
"SHARES*PRICE as STOCKVALUE " +
"FROM STOCK INNER JOIN PORTFOLIO ON " +
"STOCK.SYMBOL = PORTFOLIO.SYMBOL " +
"WHERE ID='Harold';");
var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
xmlDoc.async = false;
rs.Save (xmlDoc, 1) ; // Convert rs recordset to XML tree
xmlDoc.save(Server.MapPath("FromPortfolio.xml"))
var xsl = Server.CreateObject("Msxml2.DOMDocument");
xsl.async = false; // Convert XML tree using XSL
xsl.load(Server.MapPath("PortfolioContent.htm"))
// Write tree as HTML
Response.Write(xmlDoc.transformNode(xsl));
conn.Close();
%>
Partial XML produced by rs.Save (xmlDoc, 1); <xml> <rs:data> <z:row SYMBOL="GMC" PRICE="38.1000" STOCKVALUE="762.0000"/> <z:row SYMBOL="FORD" PRICE="15.6700" STOCKVALUE="235.0500"/> </rs:data> </xml> |
Exercise 4.5 - Computing New Fields
|
We've seen that XML can be generated dynamically in ASP from a database and
transformed to HTML by XSL. This section examines how to modify the XML, XSL or
HTML in a parse tree directly. For example, a user name can be used to
personalize a HTML page; the HTML file is parsed into the tree, a node for user
name inserted, and the HTML then output to produce the personalized HTML page. 
In the example below, the <Name> element text of file NameEdit.htm is assigned new text of 'Ray Wisman' and written to browser.
Points of note:
<%@ LANGUAGE = JScript %>
<%
// Load the HTML file into parse tree
var htmlDOC = Server.CreateObject("MSXML2.DOMDocument");
htmlDOC.async = false;
htmlDOC.load(Server.MapPath("EditNode.htm"));
// root of document var root = htmlDOC.documentElement; // Make current node "body/Name"
var node = root.selectSingleNode("body/Name");
// Assign current node text to 'Ray Wisman' node.text = 'Ray Wisman'; // Write tree as XML Response.Write(htmlDOC.xml); %> |
EditNode.htm
<html xmlns="http://www.w3.org/1999/xhtml"> HTML output <html xmlns="http://www.w3.org/1999/xhtml"> |
We have automatically converted relational database tables into XML trees before transforming using XSL, allowing us to focus on the logic in ASP rather than the user interface. XML trees can also be constructed directly. The following example defines an XML tree in which values are inserted to be displayed by XSL.
Points of note:
|
Tree construction xmlData.loadXML( |
Tree with text nodes added var node =
root.selectSingleNode("HEIGHT");
|
CreateXML.asp
<%@ Language=JScript%>
<%
var height=23.5;
var width=10.0;
var area=height*width;
var xmlData = Server.CreateObject("Microsoft.XMLDOM");
xmlData.async = false;
var xslStyle = Server.CreateObject("Microsoft.XMLDOM");
xslStyle.async = false;
xslStyle.load(Server.MapPath("Rectangle.htm"));
var root = xmlData.documentElement; // Root of XML tree
var node = root.selectSingleNode("AREA"); // AREA node
node.text = area; //<AREA>235</AREA>
var node = root.selectSingleNode("HEIGHT");
node.text = height;
var node = root.selectSingleNode("WIDTH");
node.text = width;
Response.Write(xmlData.transformNode(xslStyle));
%>
|
Rectangle.htm
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/1999/xhtml">
<xsl:output method="html" />
<xsl:template match="/">
Area of Rectangle
<xsl:value-of select="RECTANGLE/AREA" />=
<xsl:value-of select="RECTANGLE/HEIGHT" />*
<xsl:value-of select="RECTANGLE/WIDTH" />
</xsl:template>
</xsl:stylesheet>
XML data after editing AREA, HEIGHT, WIDTH HTML output Area of Rectangle 235= 23.5 * 10 |
Exercise 4.75 - Nodes and Elements
|
This approach can be used with user input from HTML forms to tailor an otherwise static HTML page for a specific purpose. In the following example, if a user makes a purchase, the <Nosale> node is deleted; if they do not make a purchase the <Sale> node is deleted.
The script consists of two parts, one to handle a sale and a no sale.
Nosale:
Sale:
The tree is then output as HTML.
<%@ LANGUAGE = JScript %>
<%
// Load the HTML
var htmlDOC = Server.CreateObject("MSXML2.DOMDocument");
htmlDOC.async = false;
htmlDOC.load(Server.MapPath("DeleteNode.htm"));
var root = htmlDOC.documentElement;
if(Request("Purchase").count == 0) { // No Purchase
var node = root.selectSingleNode("body/Sale");
var parent = node.parentNode;
parent.removeChild(node);
}
else {
var node = root.selectSingleNode("body/Nosale");
var parent = node.parentNode;
parent.removeChild(node);
var node = root.selectSingleNode("body/Sale/Purchase");
node.text = Request("Purchase");
}
Response.Write(htmlDOC.xml);
%>
DeleteNode.asp?Purchase=surf board |
DeleteNode.htm
<html xmlns="http://www.w3.org/1999/xhtml"> HTML output <html xmlns="http://www.w3.org/1999/xhtml"> |
Points of note:
A text node consists of text but not an element (i.e. <element>). In the following a text node is added whether or not a purchase is made, with appropriate text of the node. The appropriate place to add the node here is as a child of the <body> node.
The tree is then output as HTML.
<%@ LANGUAGE = JScript %>
<%
// Load the HTML
var htmlDOC = Server.CreateObject("Microsoft.XMLDOM");
htmlDOC.async = false;
htmlDOC.load(Server.MapPath("AddNode.htm"));
var root = htmlDOC.documentElement;
var parent = root.selectSingleNode("body");
var textNode;
if(Request("Purchase").count == 0) { // No Purchase
textNode = htmlDOC.createTextNode(
"Sorry we didn't have what you wanted.");
}
else {
textNode = htmlDOC.createTextNode(
"Thanks for purchasing the "
+ Request("Purchase"));
}
parent.appendChild(textNode);
Response.Write(htmlDOC.xml); %> |
AddNode.htm
<html xmlns="http://www.w3.org/1999/xhtml"> HTML output <html xmlns="http://www.w3.org/1999/xhtml"> AddNode.asp?Purchase=surf board
|
Points of note:
An element in XML is <element> </element>. An element and a text node is <element> text </element>. For simplicity, we have only diagrammed elements in the parse tree. The previous example added a text node to the tree, this example adds an element and appends a text node to the element. A node is added whether or not a purchase is made, through the text of the node is different. The appropriate place to add the node is as a child of the <body> node.
The tree is then output as HTML.
AddElement.asp
<%@ LANGUAGE = JScript %>
<%
var htmlDOC = Server.CreateObject("Microsoft.XMLDOM");
htmlDOC.async = false;
htmlDOC.load(Server.MapPath("AddElement.htm"));
var root = htmlDOC.documentElement;
var parent = root.selectSingleNode("body");
var element = htmlDOC.createElement("response");
var textNode;
if(Request("Purchase").count == 0) { // No Purchase
textNode = htmlDOC.createTextNode(
"Sorry we didn't have what you wanted.");
}
else {
textNode = htmlDOC.createTextNode(
"Thanks for purchasing the " + Request("Purchase"));
}
// AppendChild <response>
element.appendChild(textNode); // to parent <body>
parent.appendChild(element);
Response.Write(htmlDOC.xml);
%>
|
AddElement.htm
<html xmlns="http://www.w3.org/1999/xhtml"> HTML output <html xmlns="http://www.w3.org/1999/xhtml"> AddElement.asp?Purchase=boat
|
Points of note:
Exercise 5 - Nodes and Elements
|
Editing the XSL/HTML parse tree before applied to transform XML allows us to control at runtime the result of the transform. The following example deletes the element <FIRST> from the XSL parse tree, then applies the XSL tree to the XML to output as HTML.
EditXSL.asp
<%@ LANGUAGE = JScript %>
<%
var xmlData = Server.CreateObject("Microsoft.XMLDOM");
xmlData.async = false;
xmlData.load(Server.MapPath("Portfolio.xml"));
var xslDoc = Server.CreateObject("Microsoft.XMLDOM");
xslDoc.async = false;
xslDoc.load(Server.MapPath("EditXSL.htm"));
var root = xslDoc.documentElement;
var node = root.selectSingleNode(
"body/table/xsl:for-each/tr/FIRST");
var parent = node.parentNode;
parent.removeChild(node);
// Apply transformations
Response.Write(xmlData.transformNode(xslDoc));
%>
Portfolio.xml <?xml version="1.0"?>
<?xml-stylesheet type="text/xsl"
href="EditXSL.htm"?>
<Portfolio>
<STOCKID>1
<ID>Ray</ID><SYMBOL>IBM</SYMBOL>
</STOCKID>
<STOCKID>2
<ID>Ray</ID><SYMBOL>MSFT</SYMBOL>
</STOCKID>
<STOCKID>3
<ID>Harold</ID><SYMBOL>FORD</SYMBOL>
</STOCKID>
<STOCKID>4
<ID>Robin</ID><SYMBOL>UAL</SYMBOL>
</STOCKID>
<STOCKID>5
<ID>Robin</ID><SYMBOL>FORD</SYMBOL>
</STOCKID>
</Portfolio>
|
EditXSL.htm
<html xsl:version="2.0" Resulting XSL <html xsl:version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/1999/xhtml"> |
Points of note:
