Volume 3, Issue 2 - March/April 2003
   
   
 

In this monthly column, an industry expert will answer common questions about VoiceXML and related technologies. Readers are encouraged to submit questions about VoiceXML, including development, voice-user interface design, and speech technology in general, or how VoiceXML is being used commercially in the marketplace. If you have a question about VoiceXML, e-mail it to speak.and.listen@voicexmlreview.org and be sure to read future issues of VoiceXML Review for the answer.

Q: How do I use data from a Microsoft Access database in a VoiceXML application?

A: To use data from any DBMS in a VoiceXML application, you'll need to extract the data and format it in a syntax and place it in a location that a VoiceXML interpreter can fetch, parse, and execute. You have a number of options including the following:

  1. Periodically export the data from the DBMS into VoiceXML or JavaScript or some intermediary format that can be further transformed, and place the exported data on a Web server accessible to the VoiceXML interpreter.
  2. Use an API to extract and format the data directly from the DBMS on demand. The VoiceXML interpreter makes an HTTP request to a server-side script
    that in turn fetches the data and formats it for use in your voice application.

When deciding between these two options, consider the following:

  1. How frequently does the data used by your voice application change?
  2. Is it vital that users of your voice application have access to the most up to date information?
  3. Does your DBMS scale to handle the additional demand from users of your voice application?
  4. Are you prepared to secure the data in your DBMS from hackers?
If your answers to these questions are "Infrequently", "No", "No", and "No", then the first option is probably good enough.
For the purposes of this article, I'll assume that's the case. In a future column, I'll tackle the second option.

Some sample data

To help put the solution into perspective, let's define two schemas. The first describes a simple employee table;

Field Name Type Description
emp_id AutoNumber The employee's unique id (primary key)
ssn Text (9) Social Security Number (also unique)
fname Text (50) first name
lname Text (50) last name
phone Text (15) telephone number
dept_id Integer foreign key into the department table

Field Name Type Description
dept_id AutoNumber Department's unique identifier. (primary key)
name Text (50) Name of the department.
phone Text(15) Telephone number to the department's administrative assistant.

Here are a few sample records from the employee table:

emp_id ssn fname lname phone dept_id
1 022456789 "Warren" "Peace" "2121234567" 1
2 012459999 "Jane" "Doe" "4251234567" 1
3 046128877 "Jack" "Frost" "2061234567" 2
4 032576632 "Sally" "Toledo" "6501234567" 3

Here are a few sample records from the department table:

dept_id name phone
1 "Sales" "6509111001"
2 "Accounting" "6509111002"
3 "Product Support" "6509111003"

Exporting the data as XML

We'll use some of the data in these tables to build a simple company "voice dialer" application.

To do this, we'll need to extract the data to build two key components of our application: the voice user interface and the grammar.

Let's start by manually exporting the employee table to an XML file. Microsoft Access 10 makes this trivial:

  1. Open your database (.mdb).
  2. Select the table you want to export.
  3. Choose the "Export..." item under the "File" menu.
  4. In the Export Table dialog, type a name in the "file name" textbox, navigate your file system to an appropriate location, choose "XML documents" from the "Save as type" dropdown, and click "Export".
MS Access saves the entire contents of the table to an XML document. Let's take a look at how the employee table above looks:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
<employee>
     <emp_id>1</emp_id>
     <ssn>022456789</ssn>
     <fname>Warren</fname>
     <lname>Peace</lname>
     <phone>2121234567</phone>
      <dept_id>1</dept_id>
</employee>
<employee>
      <emp_id>2</emp_id>
      <ssn>012459999</ssn>
      <fname>Jane</fname>
      <lname>Doe</lname>
      <phone>4251234567</phone>
      <dept_id>1</dept_id>
      </employee>
      <employee>
      <emp_id>3</emp_id>
      <ssn>046128877</ssn>
      <fname>Jack</fname>
      <lname>Frost</lname>
      <phone>2061234567</phone>
      <dept_id>2</dept_id>
</employee>
<employee>
      <emp_id>4</emp_id>
      <ssn>032576632</ssn>
      <fname>Sally</fname>
      <lname>Toledo</lname>
      <phone>6501234567</phone>
      <dept_id>3</dept_id>
</employee>
<employee>
      <emp_id>5</emp_id>
      <ssn>044567722</ssn>
      <fname>Melvin</fname>
      <lname>Blank</lname>
      <phone>6509001234</phone>
      <dept_id>3</dept_id>
</employee>
</dataroot>


The basic structure of the generated XML document is fairly easy to decipher.
  1. The root document element's name is "dataroot".
  2. Each employee record is contained within an "employee" element.
  3. Each field in the employee table maps directly to an element by the same name.
Follow the steps above to export the department table to an XML file.

Transforming the data using XSLT

Given the data in XML format, the next step is to get this data into a format that a VoiceXML browser can interpret.

We could easily manipulate the data directly using any XML parser via a SAX or DOM interface.
Given that we want to transform the data from one schema to another, there's a better solution: XSL Transformations (XSLT).

If you're not familiar with XSLT, the XML community has made sure that you have numerous resources at your disposal. Of course, the definitive resource is the specification (http://www.w3.org/TR/1999/REC-xslt-19991116). If you find the spec intimidating, however, check out the following:

http://www.xml.com/pub/a/2000/08/holman/index.html

The best thing about XSLT, in addition to its ease of use, is the number of free XSLT processors available. Here are a few examples:
libxslt (http://www.xmlsoft.org)
Sablotron (http://www.gingerall.org)
javax.xml.transform (http://javasoft.sun.com)
MSXML (http://msdn.microsoft.com/xml)
Since MSXML comes pre-installed with Windows, we'll use that processor.

Continued...

back to the top

Copyright © 2001-2002 VoiceXML Forum. All rights reserved.
The VoiceXML Forum is a program of the
IEEE Industry Standards and Technology Organization (IEEE-ISTO).