N341 ADO XML XSL

Modified:

Basics
Access Database
XML and XSL Transformations - A Review
ASP with XML and XSL
XML and Database
Debugging
Generating Test Data
Join Database Tables
Computation
Formatting
Conditional
Building User Interfaces - include and call-template
Special Text < > and Generating Lists
Software - Generate HTML from XML/XSL transform - msxsl.exe Download

Note

Browser - IE caches files by default which can prevent opening of a later version. The effect is particularly noticeable during repeated tests when XML references an XSL file that has been changed but is not opened because of a cached version of the XSL.

The solution is to require IE to check for new versions on every visit to a page. In IE: Tools | Internet Options | Temporary Internet Files | Settings | Every Visit to Page

XML/XSL - Syntax checking of XML/XSL is rigorous, flagging errors that would be ignored in HTML by the browser. For example, the following is acceptable as HTML:

<FORM NAME=ONE>
<INPUT type=text name="TWO" >
</form>

but has the following errors as XML/XSL:

ONE must be quoted,

text must be quoted,

INPUT requires a matching end tag </INPUT>

</form> must match <FORM>

Below is valid XML/XSL:

<FORM NAME="ONE">
<INPUT type="text" name="TWO"/>
</FORM>

Basics

ASP is powerful for accessing databases and interacting with the user. However, 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 divide 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 to define much of the user interface.

First an example of Access database then a review of database tables, XML and XSL.

Access Database

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 to generate data for testing.

Exercise 0 - Access

Open Access from Start | Programs | Microsoft Access
Open File | Open... and select W:\N341\Project\Project.mdb
Select Tables | PORTFOLIO
Add 5 new records with fields ID, SYMBOL and SHARES
Save.

XML and XSL Transformations - A Review

We will 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 can be transformed using XSL into HTML for the browser. The following is the PortfolioContent.htm for the transformation.

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">
     <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>

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>
</Portfolio>

ID	Symbol
Ray	IBM
Ray	MSFT
Harold	FORD
Robin	UAL

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 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

IE browser loads and parses the XML file into a tree.
XSL style sheet file loaded and parsed to transform the tree.
Transformed XML by XSL produces an HTML result.
IE renders the HTML to display.

Portfolio.xml - Defines the XML data structure.
- <?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?> - Transform the XML using "PortfolioContent.htm" as the style sheet file.
PortfolioContent.htm - Defines the 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="Portfolio/STOCKID"> - Iterates through each of the Portfolio/STOCKID 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

Open FrontPage
Open File | New.
Select the Code tab at the bottom left.
Copy and paste Portfolio.xml from above.
Save the above Portfolio.xml to a virtual drive (i.e. W:\N341\Project).
Repeat steps 2 and 3 and save for PortfolioContent.htm
Test by loading Portfolio.xml into the browser.
Change the PortfolioContent.htm to display SHARES also.
Test by loading Portfolio.xml into the browser.

ASP with XML and XSL

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.
Pure HTML can be produced as a result of the XML/XSL transformations.
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 data structure named source from the Portfolio.xml file,
transforms the XML tree to HTML by applying the XSL in file PortfolioContent.htm,
writes the transformed tree to browser as HTML

XML and Database

XML can be generated directly and dynamically from a database by an ASP rather than coding the XML by hand. The user interface designer does not need to know the XML is generated but does need to know the XML tree structure. The following example presents:

Portfolio.xml generated by an ASP script
PortfolioContent.htm which transforms the XML into HTML
and the resulting HTML and table

The ASP:

selects all records from the Portfolio database table but only ID and SYMBOL fields
constructs the XML parse tree from the table
applies the XSL transformations to the tree from file PortfolioContent.htm. The transformation generates a table of ID and SYMBOL
writes the transformed tree to the browser as HTML

Note that database table fields are transformed to XML attributes. For example, the first table field row entry for ID and SYMBOL is XML <z:row ID="Ray" SYMBOL="IBM"/>.

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">
   <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>

Table and partial HTML produced

ID	Symbol
Ray	IBM
Ray	MSFT
Harold	FORD
Robin	UAL
Robin	FORD

<table border="1" >
  <tr>
	<td>ID</td>
	<td>Symbol</td>
  </tr>
  <tr>
	<td>Ray</td>
	<td>IBM</td>
  </tr>

Portfolio.XML

<?xml-stylesheet type="text/xsl" 
      href="PortfolioContent.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
     <z:row ID="Ray" SYMBOL="IBM" SHARES="10"/>
     <z:row ID="Ray" SYMBOL="MSFT" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="FORD" SHARES="15"/>
     <z:row ID="Robin" SYMBOL="UAL" SHARES="230"/>
     <z:row ID="Robin" SYMBOL="FORD" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="GMC" SHARES="20"/>
   </rs:data>
</xml>

Exercise 3 - XML XSL Database

Copy, paste and save PortfolioContent.htm.
Modify to output a column for SHARES.
Copy and paste Portfolio.xml to same directory.
Test by loading Portfolio.xml into the browser.

Debugging

Errors in the XML or XSL can be located by using the msxsl tool. msxsl applies XSL transformations to the XML, outputting the HTML or an error and its location. Download msxsl and save to the C:\Windows directory.

Exercise 3.2 - Debugging

Open a COMMAND prompt window.
Change to the Portfolio.xml directory.
Enter: msxsl Portfolio.xml PortfolioContent.htm
Save the HTML generated to a file by: msxsl Portfolio.xml PortfolioContent.htm > test.htm
Load test.htm into the browser.

Delete the </xsl:for-each> in PortfolioContent.htm.
Enter: msxsl Portfolio.xml PortfolioContent.htm

Generating Test Data

Where does the XML come from? An ASP can generate the XML and save to a file, useful for testing the XSL transformations. An ASP similar to the following can produce all the table entries, changing only bold table name and XML file name. The following produces a copy of database table PORTFOLIO as an XML file Portfolio.xml.

Note that the Portfolio.xml requires adding one line before useable for testing your XSL. Because the XML file defines the XSL file to use for the transformation, the following line must be added at the top of Portfolio.xml to use the PortfolioContent.htm transformation file:

<?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>

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 * FROM PORTFOLIO");

   var xmlDoc = Server.CreateObject("Msxml2.DOMDocument");
   xmlDoc.async = false;
   rs.Save (xmlDoc, 1) ;        // Convert rs recordset to XML tree
                                           // Save as XML
   xmlDoc.save(Server.MapPath("Portfolio.xml"));              
   conn.Close();
%>

Portfolio.XML

<?xml-stylesheet type="text/xsl" 
      href="PortfolioContent.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
     <z:row ID="Ray" SYMBOL="IBM" SHARES="10"/>
     <z:row ID="Ray" SYMBOL="MSFT" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="FORD" SHARES="15"/>
     <z:row ID="Robin" SYMBOL="UAL" SHARES="230"/>
     <z:row ID="Robin" SYMBOL="FORD" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="GMC" SHARES="20"/>
   </rs:data>
</xml>

Exercise 3.5 - ASP for Generating Test XML

You will need the IIS server running on a machine and the Project database name defined. Consult Setting up a Server for instructions.

Open FrontPage.
Open File | New.
Copy, paste and save the above Portfolio.asp to virtual drive W:\N341\Project.
Test by loading Portfolio.asp into the browser.
- www.ius.edu/username/Project/Portfolio.asp
- There should be a Portfolio.xml file produced.
Open Portfolio.xml and add the following line at the top:
- <?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>
Copy, paste and save the PortfolioContent.htm below to virtual drive W:\N341\Project.
Test by loading Portfolio.xml into the IE browser. The result should similar to the table at right.

ID	Symbol
Ray	IBM
Ray	MSFT
Harold	FORD
Robin	UAL
Robin	FORD

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">
     <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>

Join Database Tables

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 join data from two or more table through defined relationships.

Consider producing a trader's list of stocks and their total value. One needs 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 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 is:

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 EQUAL JOIN.

Computation

Computing new results can be done by the ASP logic or in XSL. First an example using the results of an ASP computation for the value of a stock.

Computing New Fields

The portfolio still needs a total value of each stock requiring the computation: STOCKVALUE = SHARES * PRICE. The SQL statement is:

SELECT ID, PORTFOLIO.SYMBOL, SHARES, PRICE, SHARES*PRICE as STOCKVALUE
FROM STOCK INNER JOIN PORTFOLIO ON STOCK.SYMBOL = PORTFOLIO.SYMBOL;

The resulting database table and corresponding XML is:

Exercise 4 - Computation and Testing

Open the Project.mdb file in Access
Click on Queries | Create Query in Design View
Close the Show Table dialog.
Click in menu: View | SQL View
Copy and paste the following.

SELECT ID, PORTFOLIO.SYMBOL, SHARES, PRICE, SHARES*PRICE as STOCKVALUE
FROM STOCK INNER JOIN PORTFOLIO ON STOCK.SYMBOL = PORTFOLIO.SYMBOL;

Click in menu: Query | Run
Click in menu: View | SQL View

Generating XML Test Data

Edit Portfolio.ASP of Exercise 3.5 to use the above SELECT.
Test by loading Portfolio.ASP into the browser.
Open Portfolio.xml and add the following line at the top as in the example below:
- <?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>
Test by loading Portfolio.xml into the browser.

Portfolio.XML

<?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
      <z:row ID="Ray" SYMBOL="MSFT" SHARES="100" PRICE="10.1100" STOCKVALUE="1011.0000"/>
      <z:row ID="Harold" SYMBOL="GMC" SHARES="20" PRICE="38.1000" STOCKVALUE="762.0000"/>
      <z:row ID="Harold" SYMBOL="FORD" SHARES="15" PRICE="15.6700" STOCKVALUE="235.0500"/>
      <z:row ID="Robin" SYMBOL="FORD" SHARES="100" PRICE="15.6700" STOCKVALUE="1567.0000"/>
      <z:row ID="Robin" SYMBOL="UAL" SHARES="230" PRICE="7.6400" STOCKVALUE="1757.2000"/>
      <z:row ID="Ray" SYMBOL="IBM" SHARES="10" PRICE="15.0500" STOCKVALUE="150.5000"/>
   </rs:data>
</xml>

Exercise 4.5 - XML XSL

Copy and paste PortfolioContent.htm from Exercise 3.5, if not already done so.
Modify to output columns for PRICE, SHARES and STOCKVALUE.
Test by loading Portfolio.xml from Exercise 4 into the IE browser, it should appear as at right.

ID	Symbol	Shares	Price	Stock Value
Ray	MSFT	100	10.1100	1011.0000
Harold	GMC	20	38.1000	762.0000
Harold	FORD	15	15.6700	235.0500
Robin	FORD	100	15.6700	1567.0000
Robin	UAL	230	7.6400	1757.2000
Ray	IBM	10	15.0500	150.5000

Formatting

The formatting of the PRICE and STOCKVALUE numbers should be output as currency. XSL provides a general numeric formatting similar to Visual Basic. For example, to display the string "1757.2000" as $1,757.20 in XSL:

format-number("1757.2000", '$#,###,###.00')

More generally, to format STOCKVALUE as a dollar amount:

<xsl:value-of select="format-number(@STOCKVALUE, '$#,###,###.00')" />

Exercise 5 - Formatting

Modify PortfolioContent.htm from Exercise 4 to format PRICE and STOCKVALUE as dollar values.
Delete the ID column.
Test by loading Portfolio.xml into the browser, it should appear as at right.

Modify to compute and format the PRICE*SHARES in XSL for the Stock Value rather than using STOCKVALUE.

Symbol	Shares	Price	Stock Value
MSFT	100	$10.11	$1,011.00
GMC	20	$38.10	$762.00
FORD	15	$15.67	$235.05
FORD	100	$15.67	$1,567.00
UAL	230	$7.64	$1,757.20
IBM	10	$15.05	$150.50

Portfolio.XML

<?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
      <z:row ID="Ray" SYMBOL="MSFT" SHARES="100" PRICE="10.1100" STOCKVALUE="1011.0000"/>
      <z:row ID="Harold" SYMBOL="GMC" SHARES="20" PRICE="38.1000" STOCKVALUE="762.0000"/>
      <z:row ID="Harold" SYMBOL="FORD" SHARES="15" PRICE="15.6700" STOCKVALUE="235.0500"/>
      <z:row ID="Robin" SYMBOL="FORD" SHARES="100" PRICE="15.6700" STOCKVALUE="1567.0000"/>
      <z:row ID="Robin" SYMBOL="UAL" SHARES="230" PRICE="7.6400" STOCKVALUE="1757.2000"/>
      <z:row ID="Ray" SYMBOL="IBM" SHARES="10" PRICE="15.0500" STOCKVALUE="150.5000"/>
   </rs:data>
</xml>

Computing in XSL

It is often necessary to compute values of database fields, such as the total value of a portfolio. This can be done in XSL using the sum() function on the STOCKVALUE. The following statement performs the summation.

<xsl:value-of select="sum(xml/rs:data/z:row/@STOCKVALUE)" />

One question is where to place the statement? The table below indicates that the sum() function should not be performed for each iteration but outside the iteration of each row. The following XSL fragment adds a single row after iterating through the table using the for-each:

<xsl:for-each select="xml/rs:data/z:row">
<tr>
<td><xsl:value-of select="@SYMBOL" /></td>
<td><xsl:value-of select="@SHARES" /></td>
</tr>
</xsl:for-each>
<tr> Empty row
</tr>

Exercise 6 - Summation

Modify PortfolioContent.htm from Exercise 5 to sum STOCKVALUE.
Test by loading Portfolio.xml into the IE browser.
Format the sum as dollar values. Hint: format the sum, don't sum the format.
Test by loading Portfolio.xml into the browser, it should appear as at right.

Symbol	Shares	Price	Stock Value
MSFT	100	$10.11	$1,011.00
GMC	20	$38.10	$762.00
FORD	15	$15.67	$235.05
FORD	100	$15.67	$1,567.00
UAL	230	$7.64	$1,757.20
IBM	10	$15.05	$150.50
		Total	$5,482.75

Conditional

The following uses XSL to display only the SHARES > 20 and SHARES < 200 (note that < must be represented as the HTML code < and > can optionally be >).

XSL has two conditionals, an if to test when a condition is true and a choose which performs the if-else in the fashion of a switch statement (i.e. there is no if-else in XSL).

The following example:

Applies the XSL transformations to the tree from file PortfolioContent.htm to the Portfolio.xml file.
The transformation generates a table of ID, SYMBOL and SHARES where SHARES > 30 and SHARES < 200.

Note that:

XSL has boolean operations of: and, or, not. These are the highest precedence.
Relations are: =, !=, >, >=, < is written < etc. (e.g. <= is <=). Precedence of relations is below operations.
For example:
- <xsl:if test="(@SHARES < 200) and (@SHARES > 30)">

Portfolio.XML

<?xml-stylesheet type="text/xsl" href="PortfolioContent.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
      <z:row ID="Ray" SYMBOL="MSFT" SHARES="100" />
      <z:row ID="Harold" SYMBOL="GMC" SHARES="20" />
      <z:row ID="Harold" SYMBOL="FORD" SHARES="15" />
      <z:row ID="Robin" SYMBOL="FORD" SHARES="100" />
      <z:row ID="Robin" SYMBOL="UAL" SHARES="230" />
      <z:row ID="Ray" SYMBOL="IBM" SHARES="10" />
   </rs:data>
</xml>

Table produced

ID	Symbol	Shares
Ray	MSFT	100
Robin	FORD	100

HTML

<table border="1">
<tr>
<td>ID</td>
<td>Symbol</td>
<td>Shares</td>
</tr>
<tr>
<td>Ray</td>
<td>MSFT</td>
<td>100</td>
</tr>
<tr>
<td>Robin</td>
<td>FORD</td>
<td>100</td>
</tr>
</table>

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">
    <table border="1" >
       <tr>
          <td>ID</td>
          <td>Symbol</td>
          <td>Shares</td>
        </tr>
       <xsl:for-each select="xml/rs:data/z:row">
        <xsl:if test = "@SHARES > 20 and @SHARES &lt; 200">
          <tr>
            <td><xsl:value-of select="@ID" /></td>
            <td><xsl:value-of select="@SYMBOL" /></td>
            <td><xsl:value-of select="@SHARES" /></td>
           </tr>
         </xsl:if>  
       </xsl:for-each>
     </table>
  </xsl:template>

  <xsl:template match="/">
       <xsl:call-template name="PortfolioContent" />
  </xsl:template>
</xsl:stylesheet>

Exercise 7 - XSL if

Copy and paste PortfolioContent.htm file.
Modify to display only records for ID='Harold' (use an ID in your table).
- Relations are: =, !=, >, >=, < is written < etc. (e.g. <= is <=).
Test by loading Portfolio.XML into the IE browser. It should appear as at right.

ID	Symbol	Shares
Harold	GMC	20
Harold	FORD	15

choose

Similar to the switch statement of C++ or Java, provides multiple condition tests.

The following prints a SHARES field when over 35, otherwise prints "Buy more stock!".

Partial HTML and Table produced

ID	Symbol	Shares
Ray	IBM	Buy more stock!
Ray	MSFT	100
Harold	FORD	Buy more stock!
Robin	UAL	230
Robin	FORD	100

<table border="1">
<tr>
<td>ID</td>
<td>Symbol</td>
<td>Shares</td>
</tr>
<tr>
<td>Ray</td>
<td>IBM</td>
<td>
Buy more stock!
</td>
</tr>

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">
   <table border="1">
     <tr>
        <td>ID</td>
        <td>Symbol</td>
        <td>Shares</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>
           <td>
               <xsl:choose>
                 <xsl:when test="@SHARES > 35">
                     <xsl:value-of select="@SHARES" />
                 </xsl:when>
                 <xsl:otherwise>
                        Buy more stock!
                 </xsl:otherwise>
                </xsl:choose>
           </td>
         </tr>
        </xsl:for-each>
       </table>
     </xsl:template>

     <xsl:template match="/">
        <xsl:call-template name="PortfolioContent" />
     </xsl:template>
</xsl:stylesheet>

Exercise 8 - XSL Conditional

Copy and paste PortfolioContent.htm file.
Modify to output "Cheap" when SHARES < 15, "Reasonable" when SHARES >= 15 and SHARES < 50, otherwise output "Great!".
- XSL has boolean operations of: and, or, not. These are the highest precedence.
- Relations are: =, !=, >, >=, < is written < etc. Precedence of relations is below operations.
- For example: <xsl:when test="(@SHARES < 35) or (@SHARES > 45)">
Test by loading Portfolio.XML into the browser.

Building User Interfaces - include and call-template

The XML and XSL combination separates the data from the presentation and is useful for generating tables but what about the rest of the user interface. Consider building a simple interface consisting of a table with a few hyperlinks at the top of the page and a portfolio table as content.

We'll use our earlier Portfolio.xml and PortfolioContent.htm files. Portfolio.xml is modified to reference the XSL file Portfolio.htm.

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">
     <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>

Table and partial HTML produced

ID	Symbol
Ray	IBM
Ray	MSFT
Harold	FORD
Robin	UAL
Robin	FORD

<table border="1" >
  <tr>
	<td>ID</td>
	<td>Symbol</td>
  </tr>
  <tr>
	<td>Ray</td>
	<td>IBM</td>
  </tr>

Portfolio.XML

<?xml-stylesheet type="text/xsl" 
      href="Portfolio.htm"?>
<xml 
   xmlns:rs="urn:schemas-microsoft-com:rowset" 
   xmlns:z="#RowsetSchema">
   <rs:data>
     <z:row ID="Ray" SYMBOL="IBM" SHARES="10"/>
     <z:row ID="Ray" SYMBOL="MSFT" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="FORD" SHARES="15"/>
     <z:row ID="Robin" SYMBOL="UAL" SHARES="230"/>
     <z:row ID="Robin" SYMBOL="FORD" SHARES="100"/>
     <z:row ID="Harold" SYMBOL="GMC" SHARES="20"/>
   </rs:data>
</xml>

Portfolio.htm below is the complete user interface. It implements the menu bar and includes the PortfolioContent.htm file for the content.

<xsl:include href="PortfolioContent.htm"/> - Copies the contents of PortfolioContent.htm into the file.
<xsl:template name="topNavigation"> - Defines the template "topNavigation".
<xsl:call-template name="topNavigation" /> - Calls or executes the template "topNavigation".

Portfolio.htm

<xsl:stylesheet 
  xmlns="http://www.w3.org/1999/xhtml" 
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
  version="2.0">

  <xsl:include href="PortfolioContent.htm"/>

  <xsl:template name="topNavigation">
     <table  bgColor="fuchsia" border="0">
      <tr>
         <TD><A href="welcome.xml">Welcome</A></TD>
         <td><A href="Register.xml">Register</A></td>
         <td><A href="Login.xml">Login</A></td>
         <TD><A href="Logout.asp">Logout</A></TD>
      </tr>
    </table>
  </xsl:template>

  <xsl:template match="/">
      <xsl:call-template name="PortfolioContent" />
       <xsl:call-template name="topNavigation" />
  </xsl:template>

</xsl:stylesheet>

Exercise 9 - include and call-template

Copy and paste PortfolioContent.htm, Portfolio.xml and Portfolio.htm to W:\N341\Project directory.

Test by loading Portfolio.XML into the IE browser. There is a small problem with the location of the top navigation. Correct the problem.
Modify Portfolio.htm so that the topNavigation template is included from a separate file rather than embedded.
1. Cut out the topNavigation template.
2. Open new file and paste.
3. Add <xsl:stylesheet...> and </xsl:stylesheet> tags at top and bottom.
4. Save as topNavigation.htm
Test by loading Portfolio.XML into the IE browser.

Special Text < > and Generating Lists

Pull-down selection lists are an important means for limiting users to a set of valid input choices. These can be generated from database XML by XSL.

For example, consider a selection list where the user selects the stock name to send the stock symbol to a server. The list of available stocks from the Stock database table at right and the hard-coded HTML is below.

Try selecting a stock from the list, click Submit, and check the address line for stock symbol.

<form method="get" action="">
   <select size="1" name="D1">
      <option value='MSFT'>Microsoft</option>
      <option value='GMC'>General Motors</option>
      <option value='FORD'>Ford Motor</option>
      <option value='UAL'>United Airlines</option>
      <option value='IBM'>IBM</option>
   </select>
   <input type="submit" value="Submit" name="B1"/>
   <input type="reset" value="Reset" name="B2" />
</form>

XSL can generate the select list from XML but it takes a little effort due to the < and > characters needed for the option fields. We need to generate the following from the XML:

<option value='MSFT'>Microsoft</option>

The XSL we would like to write selects the SYMBOL and NAME of each stock in the Stock table, something as below:

<xsl:for-each select="xml/rs:data/z:row">
   <option value='<xsl:value-of select="@SYMBOL" />'>
                         <xsl:value-of select="@NAME" />
   </option>
</xsl:for-each>

Unfortunately < and > have special meaning in XSL, the parser gets lost at the second < of: <option value='<xsl.

What is needed is a way to insert text such as < and > which is ignored by XSL.

Our first attempt might be to use the < for < and > for >. The XSL would be:

<xsl:for-each select="xml/rs:data/z:row">
   &lt;option value='<xsl:value-of select="@SYMBOL" />'&gt;
                           <xsl:value-of select="@NAME" />
   &lt;/option&gt;
</xsl:for-each>

but the result would be:

&lt;option value='MSFT'&gt;Microsoft&lt;/option&gt;

rather than:

```
<option value='MSFT'>Microsoft</option>
```

The solution is the <xsl:text> </xsl:text> tag which states that the between text should be ignored by XSL, and using the < and > and telling XSL to convert the < to < and > to >.

The <xsl:text disable-output-escaping="yes">'></xsl:text> converts the > to >. Whew!

The following shows the correct XSL to produce the select list above from the XML. The good news is that only the fields NAME and SYMBOL would need be changed to work with another database table fields.

SelectList.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="SelectList">
   <form method="get" action="ListGet.asp">
     <select size="1" name="D1">
       <xsl:for-each select="xml/rs:data/z:row">
         <xsl:text disable-output-escaping="yes">&lt;option value='</xsl:text>
         <xsl:value-of select="@SYMBOL" />
         <xsl:text disable-output-escaping="yes">'&gt;</xsl:text>
         <xsl:value-of select="@NAME" />
         <xsl:text disable-output-escaping="yes">&lt;/option></xsl:text>
       </xsl:for-each>
     </select>
     <input type="submit" value="Submit" name="B1" /> 
     <input type="reset" value="Reset" name="B2" />
   </form>
 </xsl:template>

  <xsl:template match="/">
       <xsl:call-template name="SelectList" />
  </xsl:template>

</xsl:stylesheet>

Resulting HTML

<form method="get" action="ListGet.asp">
<select size="1" name="D1">
    <option value="MSFT">Microsoft</option>
    <option value="GMC">General Motors</option>
    <option value="FORD">Ford Motor</option>
    <option value="UAL">United Airlines</option>
    <option value="IBM">IBM</option>
</select>
<input type="submit" value="Submit" name="B1" />
<input type="reset" value="Reset" name="B2" />
</form>

Stock.XML

<?xml-stylesheet type="text/xsl" 
      href="SelectList.htm"?>
<xml 
  xmlns:rs="urn:schemas-microsoft-com:rowset" 
  xmlns:z="#RowsetSchema">
 <rs:data>
  <z:row SYMBOL="MSFT" NAME="Microsoft" />
  <z:row SYMBOL="GMC" NAME="General Motors" />
  <z:row SYMBOL="FORD" NAME="Ford Motor" />
  <z:row SYMBOL="UAL" NAME="United Airlines" />
  <z:row SYMBOL="IBM" NAME="IBM" />
 </rs:data>
</xml>