XML

XML (eXtensible Markup Language) has been designed specifically to represent data so it can be transmitted from one computer to another. It is, as its name reveals, a structured markup language like HTML but unlike HTML it has no display capabilities and any display of the data must be supplied by other means. XML, therefore, is primarily an Internet technology but it belongs in a discussion of databases because it is used for storing and transmitting data and the Internet provides an excellent way to transmit structured data.

An example of an XML document might be:

<invoices>
<itemrec>
<invoice_id>1</invoice_id>
<date>12/07/2003</date>
<payee>Smiths</payee>
<total_amount>65.76</total_amount>
</itemrec>
</invoices>

The <itemrec> object could be repeated for every record (row) in a table. XML documents can be far more complex than this simple table-record-field structure, the nesting can be far deeper and they can contain natural language documents such as play scripts, essays and books. Whatever the content of an XML document the rules for defining its structure are stricter than those for HTML.

As we can see from the example above data in XML is surrounded by pairs of tags such as <payee>Smiths</payee>. Such an arrangement is commonly called a node in XML. An XML document must be well-named and well-formed, that is its tags must follow the naming rules (similar to those for variables in any programming language) and the tags must be arranged in a precise hierarchical manner. In HTML one can write <b><i>word</b></i> but in XML the pairs of tags must be arranged in strict sequence (<b><i>word</i></b>).

An XML document like that above could be processed as strings, searching for tags and treating them accordingly when they are found. On the other hand many XML objects already have their own processing capabilities and these can be used without the need for bespoke programming. Creating XML is straightforward because it is plain text and contains no proprietary database information: it provides an open standard for defining data structures and processing them. Reading XML source documents is a little more complicated because the code must be parsed, that is the nodes must be picked apart and a structure created in memory that corresponds to that in the document, which is then used to store the data. Microsoft provide a XML parser called msxml.dll, which is supplied with Internet Explorer version 5 and above.

XML pages can be generated in various ways:

created directly in a text editor
saved from an ADO object in a database management application
saved from an ADO object in an ASP page
saved directly from MS Access (version XP onwards).

The last three produce similar results because, at least in the Microsoft world, they use the same basic objects. XML can be processed in a range of ways, depending partly on the way the XML data was created.

Document Type Definition (DTD)

One way of processing an XML document is to set up a Document Type Definition (DTD), which will inform the parser what tags to expect and what values are valid for a each tag. A separate DTD will have to be produced for each document processed. If an XML document is to be used frequently, as in the case of an on-line form, it may be worth the effort of using a DTD to process the volume of documents received.

The XML Document Object

In Microsoft systems an XML document is created and loaded with an ActiveX control called Microsoft.XMLDOM (DOM=Document Object Model). The XMLDOM object has properties and methods that can be used to control the reading and writing of documents in XML format. The following lines create an XMLDOM and load it into memory:

Dim xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
xmldoc.loadXML("filename")

In JavaScript:

var xmldoc = new ActiveXObject("Microsoft.XMLDOM")

The "filename" part of the loadXML method can be a file (invoices.xml), a url or a local string made up of XML tags.

The most significant property of an XML document is the async property which determines whether an XML document is loaded synchronously (in one go before anything else happens) or asynchronously (lines of XML interleaved with other code such as HTML).

Processing XML in a Browser

The following code reads a single record from an XML document in the form given above (invoice). This program is designed to run on the client side of a browser so it is written in HTML and JavaScript.

<body>
<h2>Invoices</h2>
<script language="JavaScript">
//variables for XML tags
var id, date, payee, amount
var rootElem;
// Create and load XML DOM object.
var xmlDoc = new ActiveXObject("microsoft.xmldom");
xmlDoc.async = false;
xmlDoc.load("invoicesone.xml");
// If successful get data values from first record.
// Otherwise, display error message from parser.
if (xmlDoc.parseError == 0) {
rootTag = xmlDoc.documentElement;
id=rootTag .childNodes.item(0).childNodes.item(0).text;
date=rootTag .childNodes.item(0).childNodes.item(1).text;
payee=rootTag .childNodes.item(0).childNodes.item(2).text;
amount=rootTag .childNodes.item(0).childNodes.item(3).text;
}else{
alert(xmlDoc.parseError.reason);
}
</script>
<table border="0" cellspacing="4">
<tr><th>Field</th><th>Value</th></tr>
<tr><td>Invoice ID:</td><td><script>document.write(id)</script></td></tr>
<tr><td>Date:</td><td><script>document.write(date)</script></td></tr>
<tr><td valign="top">Payee:</td><td><script>document.write(payee)</script></td></tr>
<tr><td>Total Amount:</td><td><script>document.write(amount)</script></td></tr>
</table>
</body>

The line:

xmlDoc.load("invoicesone.xml");

could have been written as:

xmlDoc.load("http://cph-laptop/xml/invoicesone.xml");

Here a URL on a web server has been used in place of the file name.

One Invoice Demonstration

The XML Data Source Object (DSO)

An alternative way of processing an XML document is to use an XML Data Source Object (DSO). The following code loads an XML DSO into the <body> section of a HTML document:

This definition could be extended to include the text of the XML document:

<OBJECT width=0 height=0
classid="clsid:550dda30-0541-11d2-9ca9-0060b0ec3d39"
id="dsoeg1">
<invoices>
<itemrec>
<invoice_id>1</invoice_id>
<date>12/07/2003</date>
<payee>Smiths</payee>
<total_amount>65.76</total_amount>
</itemrec>
</invoices>
</OBJECT>

More likely, the XML document will be loaded from a file:

<body onload="OpenDSO();">
<object width="0" height="0" id="xmldso" classid="clsid:550dda30-0541-11d2-9ca9-0060b0ec3d39">
</object>
<SCRIPT>
function OpenDSO() {
xmldso.XMLDocument.load("invoicesone.xml");
if (xmldso.XMLDocument.parseError != 0) {
alert("XML load failed.");
} }
</SCRIPT>
<table>
<tr><td>Invoice ID:</td>
<td><span datasrc="#xmldso" datafld="invoice_id"></span></td></tr>
<tr><td>Date:</td>
<td><span datasrc="#xmldso" datafld="date"></span></td></tr>
<tr><td>Payee:</td>
<td><span datasrc="#xmldso" datafld="payee"></span></td></tr>
<tr><td>Amount:</td>
<td><span datasrc="#xmldso" datafld="total_amount"></span></td></tr>
</table>
</body>

In this example the XML document is created and loaded in the line xmldso.XMLDocument.load("invoicesone.xml");. The data in the DSO is bound to HTML table elements using the <span> tag with a datasrc="#xmldso" datafld="payee" extension.

One Invoice DSO Demonstration

XML Data Islands

Perhaps the easiest way of processing XML data in an HTML document is by means of an XML Data Island. This consists of just one line in the <body> section of an HTML document:

Once again the text of the XML document could be placed between the <XML> and </XML> tags but in most cases a file will probably be used, for example:

<body>
<XML Id="xmldso" src="invoicestwo.xml"></XML>
<h2 align="center">Invoices</h2>
<table datasrc="#xmldso" cellspacing="4">
<thead>
<tr>
<th width="100" align="left">Invoice ID</th>
<th width="100" align="left">Date</th>
<th width="100" align="left">Payee</th>
<th width="100" align="left">Amount</th>
</thead>
<tr>
<td valign="top" width="100" align="left"><span datafld="invoice_id"></span></td>
<td valign="top" width="100" align="left"><span datafld="date"></span></td>
<td valign="top" width="100" align="left"><span datafld="payee"></span></td>
<td valign="top" width="100" align="left"><span datafld="total_amount"></span></td>
</tr>
</table>
</body>

Note how a datasrc is specified in the <table> tag and a datafld item is embedded in the <span> tags. This code processes all records in a file - in this case two records.

Two invoices using a Data Island

XML Schemas

XML code saved from Delphi, through ADO in an ASP page and through MS Access has a different structure to what a user might create directly in a text editor, it includes the XML data schema as the first root element:

The second element in this case is called <rs:data> and the data itself is placed in rows labelled <z:row>. Notice the minus symbols which are used to collapse the nodes (plus symbols are used to expand them).

An XML document in this format can be processed in the same ways as before, but this time allowance must be made for the extra <s:schema> tag. Key lines in this code are:

xmlRsData = xmlDoc.documentElement.childNodes.item(1);

This line sets a variable to the second childNode element of the document (zero indexing so the index is '1').

for (i=0; i<xmlRsData.childNodes.length; ++i){

This line sets up a loop to move through the items in the childNodes of the second major tag (<rs:data>.

col0 = xmlRsData.childNodes.item(i).getAttribute("c0");

This line retrieves the attribute stored in the <z:row> accessed by the loop counter and called "c0" (column zero).

<body>
<h2>Invoices</h2>
<script language="JavaScript">
var id, date, payee, amount
var xmlRsData;
var i
// Create and load XML DOM object. Can be done in various ways!
var xmlDoc = new ActiveXObject("microsoft.xmldom");
xmlDoc.async = false;
xmlDoc.load("invoices.xml");
// If successful, retrieve data values from the first record.
// Otherwise, display the error message from the parser.
if (xmlDoc.parseError == 0){
xmlRsData = xmlDoc.documentElement.childNodes.item(1);
for (i=0; i<xmlRsData.childNodes.length; ++i){
id = xmlRsData.childNodes.item(i).getAttribute("c0");
date= xmlRsData.childNodes.item(i).getAttribute("Date");
payee= xmlRsData.childNodes.item(i).getAttribute("Payee");
amount= xmlRsData.childNodes.item(i).getAttribute("c3");

document.write("<table border='0' cellpadding cellspacing='5'>");
document.write("<tr><th>Field</th> <th>Data</th> </tr>");
document.write("<tr><td>Record ID:</td>");
document.write("<td>"+ id + "</td></tr>");
document.write("<tr><td>Date:</td>");
document.write("<td>"+ date + "</td></tr>");
document.write("<tr><td>Payee:</td>");
document.write("<td>"+ payee + "</td></tr>");
document.write("<tr><td>Total:</td>");
document.write("<td>"+ amount + "</td></tr>");
document.write("</table>");
}
}
else{
alert(xmlDoc.parseError.reason);
}
</script>
</body>

As this is just script this file will work from either a browser (.html) or a server (.asp).

Multiple Invoices Demonstration

XML and Servers

Reading a Single Record from a Server

XML has no display capabilities so it makes good sense to source documents from a server. Data in XML format can be read from a server as well as from a client. Here is some basic XML:

<invoices>
<itemrec>
<invoice_id>1</invoice_id>
<date>12/07/2003</date>
<payee>Smiths</payee>
<total_amount>65.76</total_amount>
</itemrec>
<itemrec>
<invoice_id>2</invoice_id>
<date>13/07/2003</date>
<payee>Jones</payee>
<total_amount>237.52</total_amount>
</itemrec>
</invoices>

The HTML/ASP code to display the first record, but not the second, might be as follows:

<html><head></head>
<body>
<h2>A Single Invoice from the Server</h2>
<%
Set xmlDoc=Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async=False
xmlDoc.load(Server.MapPath("invoicestwo.xml"))
if xmlDoc.parseError then
Response.Write "<p class=err>" & xmlDOc.parseError.reason & "</p>" & vbCrLf
Else
Set rootElem=xmlDoc.documentElement
id=rootElem.childNodes.item(0).childNodes.item(0).text
date=rootElem.childNodes.item(0).childNodes.item(1).text
payee=rootElem.childNodes.item(0).childNodes.item(2).text
amount=rootElem.childNodes.item(0).childNodes.item(3).text
%>
<table border="0" cellpadding cellspacing="5">
<tr><th>Field</th><th>Data</th></tr>
<tr><td>Record ID:</td>
<td><%=id%></td>
</tr>
<tr><td>Date:</td>
<td><%=date%></td>
</tr>
<tr><td valign="top">Payee:</td>
<td><%=payee%></td>
</tr>
<tr><td>Total:</td>
<td><%=amount%></td>
</tr>
</table></body></html>
<%
End If
%>

This code creates an XML DOM object and loads the XML file shown above into it. It then sets a variable, rootElem, to the first node in the XML data, <invoices>, and seeks out the child node <itemrec> and the four child nodes of this node that hold the data. The statement

id=rootElem.childNodes.item(0).childNodes.item(0).text

sets the variable id to the child of the child of the root node, that is: <invoices> -> <itemrec> -> {<invoice_id>, <date>, <payee>, <total_amount>}. The other variables are found in the same way. The file then outputs a table with the variable names embedded in it.

Display Invoice from server

Generating XML from a Server

This code provides the means to display XML on screen:

<% Response.ContentType = "text/xml" %>

<%
'Response.Write "<?xml version=" & chr(34) & "1.0" & chr(34) & "?>" & vbCrLf
' Create and open ADO connection object.
Set cnInv = Server.CreateObject("ADODB.Connection")
cnStrInv = "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=" & Server.MapPath("invoices.mdb")
cnInv.Open cnStrInv,"",""
' Create and open ADO recordset object.
Set rsItm = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Invoices WHERE Payee='Hansons' ORDER BY Date"
rsItm.Open sql, cnInv, adOpenStatic, adLockReadOnly
Dim strm
Set strm = CreateObject("ADODB.Stream")
rsItm.Save strm, adPersistXML
XMLtext = strm.ReadText
Response.Write XMLtext
rsItm.Close
cnInv.Close
%>

This code first announces that its content type is XML and it then asks for a file, adovbs.inc, to be included in the text (this file cotains definitions of ADO variables). The code then inserts an XML tag into the output stream, along with the version number in use. The code then opens an ADO Connection and Record Set, sets up a simple SQL query and opens the table from that query. The ADODB.Stream command was introduced in Microsoft MDAC 2.5 (which comes with Windows 2000). The output from the SQL query operating on the table is directed to an XML target, which is then output to the screen in Internet Explorer.

Invoices in XML from server

Reading Multiple Records from a Server

<body>
<h2>Invoices</h2>
<script language="JavaScript">
var id, date, payee, amount
var xmlRsData ;
var i
// Create and load XML DOM object.
var xmlDoc = new ActiveXObject("microsoft.xmldom");
xmlDoc.async = false;
xmlDoc.load("invoices.xml");
// If successful, retrieve data values from the first record.
// Otherwise, display the error message from the parser.
if (xmlDoc.parseError == 0){
xmlRsData = xmlDoc.documentElement.childNodes.item(1);
for (i=0; i<xmlRsData.childNodes.length; ++i){
id= xmlRsData.childNodes.item(i).getAttribute("c0");
date= xmlRsData.childNodes.item(i).getAttribute("Date");
payee= xmlRsData.childNodes.item(i).getAttribute("Payee");
amount= xmlRsData.childNodes.item(i).getAttribute("c3");
document.write("<table border='0' cellpadding cellspacing='5'>");
document.write("<tr><th>Field</th> <th>Data</th> </tr>");
document.write("<tr><td>Record ID:</td>");
document.write("<td>"+id+ "</td></tr>");
document.write("<tr><td>Date:</td>");
document.write("<td>"+date+ "</td></tr>");
document.write("<tr><td>Payee:</td>");
document.write("<td>"+payee+ "</td></tr>");
document.write("<tr><td>Total:</td>");
document.write("<td>"+amount+ "</td></tr>");
document.write("</table>");
}
}
else{
alert(xmlDoc.parseError.reason);
}
</script>
<p><a href="../default.htm">Back</a></p>
</body>

A key line in this code is the following, where a variable is set to the second main item in the XML document:

xmlRsData = xmlDoc.documentElement.childNodes.item(1);

The first major element, childNodes.item(0), is <s:Schema id='RowsetSchema'>, which is closed with </schema>. The second major element is <rs:data>, which includes the <z:row> elements, referred to here as childNodes.item(1). To access the data stored within each <z:row> tag we set up a loop that will move from the first such tag to the last:

for (i=0; i<xmlRsData.childNodes.length; ++i){

We then set a variable to each item within a <z:row>:

id= xmlRsData.childNodes.item(i).getAttribute("c0");

Still within the loop we now send each variable to a HTML table cell:

document.write("<td>"+ id + "</td></tr>");

This routine will thus deal with any number of <z:row> elements that have previously been saved in XML format.

Invoices (read multiple records from server)

Transmitting XML Objects

XML, in particular the Microsoft implementation of XML, provides an object, XMLHttpRequest, that can transmit and receive XML documents across the Internet. This is provided to make it easier for organisations to automate the processing of business information such as orders and confirmations. One might, for example, submit a tax form as an XML document and the Tax Office might respond to it with a document that confirms to the sender the information they have supplied. Another example might be an order for items from a supplier, a response that confirms the order, provides an invoice and tracking information showing the progress of the goods. Another application might be the return of information from a database. One advantage of XML in these situations is that it passes along the Internet and through firewalls like HTML.

In the example here a request is made from a form in a HTML document for an invoice item from a server that holds the database. The invoice numbers requested are composed into an XML document, which is then sent from the client to the server. The server reads the XML document, reads the numbers and compares them with items in the database and returns those with matching ID numbers. This automates the process of sending XML and receiving it in the same format. This forms the basis of much business communication now and it grow in importance in the future.

Send request and receive data back in XML

<html><head>
<title>Multiple Invoice Inquiry</title>
<script>
function AddID(){
txt = document.frmReq.txtIDnr.value;
if (txt != "") {
pos = document.frmReq.lstIDnr.options.length;
var opt = new Option (txt, txt);
document.frmReq.lstIDnr.options[pos] = opt;
document.frmReq.txtIDnr.value = "";
}
}

This HTML document runs from a client, hence the code in JavaScript. The HTML section at the bottom places a form on the screen into which the user types the numbers of the invoices to be retrieved from the database on the server. The first function in the JavaScript section, AddID, adds the numbers entered in the input box to a list of numbers. The second function, GetIDInfo, creates an XML file with a node for each of the numbers entered by the user. The code then creates an XMLHTTP object with an action of POST and a target of the corresponding ASP file on the server. After the XML has been sent the XMLHTTP object receives the response from the ASP file and, if the response is valid, build a HTML table to display the results sent from the server.

The ASP file that returns the XML data from the server to the client looks like this:

<% Response.ContentType = "text/xml" %>

<%
Response.ContentType = "text/xml"
' Create and open ADO Recordset object.
sql = "SELECT * FROM invoices ; "
cnStInv = "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=" & Server.MapPath("invoices.mdb")
Set rsInv = Server.CreateObject("ADODB.Recordset")
rsInv.Open sql, cnStInv, adOpenStatic, adLockReadOnly
Set xmlRsp = Server.CreateObject("microsoft.xmldom")
Set newNode = xmlRsp.createElement("invoices")
xmlRsp.appendchild(newNode)
Set xmlReq = server.createobject("Microsoft.xmldom")
xmlReq.load(request)
If xmlReq.parseError Then
AddCatnrNode "", "", "", xmlReq.parseError.reason
Else
Set reqRst = xmlReq.documentElement.childNodes
For rnum = 0 to reqRst.length - 1
Set reqRcd = reqRst.item(rnum).childNodes
For fnum = 0 to reqRcd.length - 1
If (reqRcd.item(fnum).tagName = "idnr") Then
FindIDnr(reqRcd.item(fnum).text)
End If
Next
Next
End If
xmlRsp.Save(Response)
rsInv.Close
Sub FindIDnr (vidnr)
If IsNumeric(vidnr) Then
rsInv.MoveFirst
rsInv.Find "InvoiceID = " & vidnr
If rsInv.EOF Then
AddIDnrNode vidnr, "Not found.", "Not found.", "Not found."
Else
AddIDnrNode vidnr, rsInv("date"), rsInv("payee"), rsInv("TotalCost")
End If
Else AddIDnrNode vidnr, "Invalid", "Invalid.", "Invalid."
End If
End Sub

Sub AddIDnrNode (vidnr, adate, adesc, tamount)
Set newNode = xmlRsp.createElement("payee")
xmlRsp.lastChild.appendChild(newNode)
Set newNode = xmlRsp.createElement("idnr")
xmlRsp.lastChild.lastChild.appendChild(newNode)
xmlRsp.lastChild.lastChild.lastChild.text = vidnr
Set newNode = xmlRsp.createElement("date")
xmlRsp.lastChild.lastChild.appendChild(newNode)
xmlRsp.lastChild.lastChild.lastChild.text = adate
Set newNode = xmlRsp.createElement("payee")
xmlRsp.lastChild.lastChild.appendChild(newNode)
xmlRsp.lastChild.lastChild.lastChild.text = adesc
Set newNode = xmlRsp.createElement("totalcost")
xmlRsp.lastChild.lastChild.appendChild(newNode)
xmlRsp.lastChild.lastChild.lastChild.text = tamount
End Sub
%>

This code opens the database object and reads the data with a SQL query. It then creates an XML DOM object and compares the number in each node sent from the user with the corresponding data item in the record set read from the database on the server. When the numbers in the XML nodes match those in the record set the data in the database is added to the XML file that is sent back to the client.

Return to Access Menu