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:
|
Table and
|
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:
We have seen how Portfolio.xml can be transformed using XSL to HTML for the browser.
The following is the PortfolioContent.xsl to perform the transformation,
yielding the same result as Portfolio.ASP earlier.
|
|
At this point, we 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
|
 |
Portfolio.xml - Defines the XML data structure.
<?xml-stylesheet type="text/xsl" href="PortfolioContent.xsl"?> -
Transform the XML using "PortfolioContent.xsl" as the style sheet file.
PortfolioContent.xsl - Defines the XSL transformations, most of which is regular HTML.
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"> - Start of style sheet that follows the www.w3.org... naming definitions.
<xsl:template match="/"> - A template for display of the '/' or root of the XML.
<xsl:for-each select="xml/rs:data/z:row"> - Iterates through each of the xml/rs:data/z:row nodes.
<xsl:value-of select="@ID" /> - Outputs the value of the current
"ID" node.
HTML - Generated by IE browser when loading the Portfolio.xml
file.
Attributes - XML elements (and HTML, which is a subset of XML) can contain attributes, for example:
<TABLE BORDER="1"> where BORDER is the attribute and "1" is the value of BORDER.
<xsl:value-of select="ID"/> where select is the attribute and "ID" is
the value.
Empty - Elements can be empty and have attributes, for example the following are equivalent:
<xsl:value-of select="@ID"/>
<xsl:value-of select="@ID"></xsl:value-of select>
Exercise 1 - XML XSL
|
Combining XML and XSL on the browser has several drawbacks:
The browser may not be XML-capable for performing the XML/XSL to HTML transformation
XML data is static rather than extracted dynamically from a database.
Combining XML and XSL on the server running an ASP program rather than on the browser client has three key advantages:
The XML can be generated dynamically by the server through user interaction, extraction from a database, etc.
HTML can be produced as a result of the XML/XSL transformations, the browser does not need to be XML-capable.
The XML/XSL tree can be modified before writing to the client.
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:
builds the XML tree named xml from the Portfolio.xml file: xml.load(Server.MapPath("Portfolio.xml"));
builds the XSL tree named xsl from the PortfolioContent.xsl file: xsl.load(Server.MapPath("PortfolioContent.xsl"));
transforms the XML tree to HTML by applying the XSL: xml.transformNode(xsl)
writes the transformed tree to browser as HTML: Response.Write
|
|
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 Table and HTML produced
|
XML saved to file FromPortfolio.xml by rs.Save (xmlDoc, 1);
|
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:
SELECT * FROM STOCK INNER JOIN PORTFOLIO ON STOCK.SYMBOL = PORTFOLIO.SYMBOL; Reducing the fields to those needed:
SELECT PORTFOLIO.ID, PORTFOLIO.SYMBOL, PORTFOLIO.SHARES, STOCK.NAME, STOCK.PRICE
FROM STOCK INNER JOIN PORTFOLIO ON STOCK.SYMBOL = PORTFOLIO.SYMBOL;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:
SELECT PORTFOLIO.ID, PORTFOLIO.SYMBOL, PORTFOLIO.SHARES, STOCK.NAME, STOCK.PRICE
FROM STOCK INNER JOIN PORTFOLIO ON STOCK.SYMBOL = PORTFOLIO.SYMBOL WHERE ID='Harold';
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.xsl 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).
Table and partial HTML produced
|
Partial XML produced by rs.Save (xmlDoc, 1);
|
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
Here we only need the SYMBOL, PRICE and computed STOCKVALUE.
The XSL file, PortfolioContent.xsl must be changed to display the STOCKVALUE field.
<%@ 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.xsl"))
// 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.xsl 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.xsl"));
// 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.xsl
<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("<RECTANGLE>" +  |
Tree with text nodes added var node =
root.selectSingleNode("HEIGHT");
|
<%@ 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.xsl"));
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 ));
%>
|
<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.xsl"));
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 |
<html xmlns="http://www.w3.org/1999/xhtml"> <body> We <b>appreciate</b> your business. <Sale> Thanks for your money. Your <Purchase></Purchase> will be in the mail. </Sale> <Nosale> Sorry that we didn't have what you wanted. </Nosale> </body> </html> 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.xsl"));
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); %> |
<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.
<%@ LANGUAGE = JScript %>
<%
var htmlDOC = Server.CreateObject("Microsoft.XMLDOM");
htmlDOC.async = false;
htmlDOC.load(Server.MapPath("AddElement.xsl"));
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);
%>
|
<html xmlns="http://www.w3.org/1999/xhtml"> <body> We <b>appreciate</b> your business. </body> </html> 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.
<%@ 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.xsl"));
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.xsl"?>
<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>
|
<html xsl:version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml"> <body> <table border="1"> <tr> <td>ID</td> <td>SYMBOL</td> </tr> <xsl:for-each select="Portfolio/STOCKID"> <tr> <FIRST> <td><xsl:value-of select="ID"/></td> </FIRST> <td><xsl:value-of select="SYMBOL"/></td> </tr> </xsl:for-each> </table> </body> </html> Resulting XSL <html xsl:version="2.0" |
Points of note:
