Searching XML Data
You can do a great many things with recordsets, as we've seen in this chapter. In this chapter's final example, I'll take a look at how to search a database for a specific item. In particular, I'll let the user search for a match to a customer's name that this user specifies.
For this example, I'll modify customer.xml by adding a second customer with the name Nancy to make sure that we catch all instances of a match, naming this new document multiple.xml:
<?xml version="1.0"?>
<CUSTOMER>
<CUSTOMER>
<NAME>Charles</NAME>
<CUSTOMER_ID>58704</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Meat</DEPARTMENT>
<PRODUCT_NAME>Ham</PRODUCT_NAME>
</CUSTOMER>
<CUSTOMER>
<NAME>Franklin</NAME>
<CUSTOMER_ID>58705</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Produce</DEPARTMENT>
<PRODUCT_NAME>Tomatoes</PRODUCT_NAME>
</CUSTOMER>
<CUSTOMER>
<NAME>Phoebe</NAME>
<CUSTOMER_ID>58706</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Meat</DEPARTMENT>
<PRODUCT_NAME>Turkey</PRODUCT_NAME>
</CUSTOMER>
<CUSTOMER>
<NAME>Mark</NAME>
<CUSTOMER_ID>58707</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Meat</DEPARTMENT>
<PRODUCT_NAME>Beef</PRODUCT_NAME>
</CUSTOMER>
<CUSTOMER>
<NAME>Nancy</NAME>
<CUSTOMER_ID>58708</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Frozen</DEPARTMENT>
<PRODUCT_NAME>Broccoli</PRODUCT_NAME>
</CUSTOMER>
<CUSTOMER>
<NAME>Nancy</NAME>
<CUSTOMER_ID>58709</CUSTOMER_ID>
<PURCHASE_DATE>10/15/2001</PURCHASE_DATE>
<DEPARTMENT>Produce</DEPARTMENT>
<PRODUCT_NAME>Tomatoes</PRODUCT_NAME>
</CUSTOMER>
</CUSTOMER>
After setting up an XML data island and connecting it to multiple.xml, I'll define a new function named findMatches that will search for matches to the customer name the user wants to search for. Although ADOR recordset objects do have a method called find for searching databases, you set the search criterion in that method using a Structured Query Language (SQL, the language many database applications use) expression, and Internet Explorer doesn't appear to support such expressions. Instead, I'll set up a JavaScript loop that will find matches to the name the user is searching for.
The user can enter the name to search for in a text field that I'll call text1. When that user clicks a button, the findMatches function is called. I'll convert the name the user wants to search for into lowercase (using the JavaScript String object's toLowerCase method) to make the search case-insensitive, and I'll store it in a variable named searchFor:
<HTML>
<HEAD>
<TITLE>
Searching XML-Based Databases
</TITLE>
<XML ID="customers" SRC="multiple.xml"></XML>
<SCRIPT LANGUAGE="JavaScript">
function findMatches()
{
var searchFor = form1.text1.value.toLowerCase()
.
.
.
Now I'll loop over all the records in the recordset, storing the name in the current record in a variable named currentName, which I also convert to lowercase:
<HTML>
<HEAD>
<TITLE>
Searching XML-Based Databases
</TITLE>
<XML ID="customers" SRC="multiple.xml"></XML>
<SCRIPT LANGUAGE="JavaScript">
function findMatches()
{
var searchFor = form1.text1.value.toLowerCase()
while (!customers.recordset.EOF) {
var currentName = new String(customers.recordset("NAME"))
currentName = currentName.toLowerCase()
.
.
.
customers.recordset.moveNext()
}
}
</SCRIPT>
</HEAD>
I'll use the JavaScript String object's indexOf method to see whether the current name matches the name that the user is searching for. The indexOf method returns a value of 0 or greater if a match was found, so I use that method like this:
<HTML>
<HEAD>
<TITLE>
Searching XML-Based Databases
</TITLE>
<XML ID="customers" SRC="multiple.xml"></XML>
<SCRIPT LANGUAGE="JavaScript">
function findMatches()
{
var searchFor = form1.text1.value.toLowerCase()
while (!customers.recordset.EOF) {
var currentName = new String(customers.recordset("NAME"))
currentName = currentName.toLowerCase()
if (currentName.indexOf(searchFor) >= 0) {
.
.
.
}
customers.recordset.moveNext()
}
}
</SCRIPT>
</HEAD>
.
.
.
All that remains is to display the matching records in a <DIV> element, as we have before in this chapter, and add the button and text field that we'll use to let the user interact with our code:
<HTML>
<HEAD>
<TITLE>
Searching XML-Based Databases
</TITLE>
<XML ID="customers" SRC="multiple.xml"></XML>
<SCRIPT LANGUAGE="JavaScript">
function findMatches()
{
var searchFor = form1.text1.value.toLowerCase()
while (!customers.recordset.EOF) {
var currentName = new String(customers.recordset("NAME"))
currentName = currentName.toLowerCase()
if (currentName.indexOf(searchFor) >= 0) {
divMessage.innerHTML +=
customers.recordset("NAME") +
" (ID " +
customers.recordset("CUSTOMER_ID") +
") bought " +
customers.recordset("PRODUCT_NAME") +
" from the " +
customers.recordset("DEPARTMENT") +
" department on " +
customers.recordset("PURCHASE_DATE") +
".<BR>"
}
customers.recordset.moveNext()
}
}
</SCRIPT>
</HEAD>
<BODY>
<CENTER>
<H1>
Searching XML-Based Databases
</H1>
<FORM ID="form1">
Search for this name: <INPUT TYPE="TEXT" NAME="text1">
<BR>
<BR>
<INPUT TYPE="BUTTON" VALUE="Search for matches"
ONCLICK="findMatches()">
</FORM>
</CENTER>
<DIV ID="divMessage">
</DIV>
</BODY>
</HTML>
You can see the results in Figure 8.9, where we've matched both customers named Nancy.
In this example, I've used XML data islands, but of course you can also use the XML DSO applet to load the file multiple.xml. I'll do that here to demonstrate another aspect of the XML DSO applet: Up until now, I've given the applet a zero width and height, but in fact, this applet does display the status of its operations if you give it some space in the Web page. I'll do that like this:
<HTML>
<HEAD>
<TITLE>
Searching XML-Based Databases
</TITLE>
<SCRIPT LANGUAGE="JavaScript">
function findMatches()
{
var searchFor = form1.text1.value.toLowerCase()
while (!customers.recordset.EOF) {
var currentName = new String(customers.recordset("NAME"))
currentName = currentName.toLowerCase()
if (currentName.indexOf(searchFor) >= 0) {
divMessage.innerHTML +=
customers.recordset("NAME") +
" (ID " +
customers.recordset("CUSTOMER_ID") +
") bought " +
customers.recordset("PRODUCT_NAME") +
" from the " +
customers.recordset("DEPARTMENT") +
" department on " +
customers.recordset("PURCHASE_DATE") +
".<BR>"
}
customers.recordset.moveNext()
}
}
</SCRIPT>
</HEAD>
<BODY>
<CENTER>
<H1>
Searching XML-Based Databases
</H1>
<APPLET CODE="com.ms.xml.dso.XMLDSO.class"
ID="customers"
WIDTH="400" HEIGHT="50"
MAYSCRIPT="true">
<PARAM NAME="URL" VALUE="multiple.xml">
</APPLET>
<FORM ID="form1">
Search for this name: <INPUT TYPE="TEXT" NAME="text1">
<BR>
<BR>
<INPUT TYPE="BUTTON" VALUE="Search for matches"
ONCLICK="findMatches()">
</FORM>
</CENTER>
<DIV ID="divMessage">
</DIV>
</BODY>
</HTML>
You can see the results in Figure 8.10, where the XML DSO is displaying the message Successfully loaded XML from "file:/C:/xml/multiple.xml". The background of this applet is green because there was no error; if an error occurred, the background would be red and the applet would display error messages.
That completes our data-binding work with the Internet Explorer. In the next chapter, I'll take a look at XML and cascading style sheets.