ODBC2XML is a database publisher that merges data from any ODBC data source into XML documents. Its unique architecture uses predefined XML templates that contain embedded SQL queries, and generates documents that contain the ODBC data merged into the original XML. It comes complete with a callable DLL and a command-line interface for batch processing.

ODBC2XML is distributed as shareware. You may obtain the ODBC2XML trial copy and use it freely on a single-user machine for any purpose. However, you may not redistribute this version for commercial use, and any SQL query that returns more than 15 rows will abort with an error message. To avoid these restrictions you must use the registered version. For a single-user license along with up to 1 hour of free technical support, you can register ODBC2XML online at RegNet. Additional licensing options are described below.

Overview

ODBC2XML simplifies the generation of complex XML documents. It allows non-programmers to generate sophisticated XML files without the need for scripting languages, report writers, or other more complex processing tools. Using ODBC2XML, you can:

• merge data from Oracle, SQL server, Microsoft Access, Excel spreadsheets, or any other data source with an ODBC driver, into XML files;
• generate XML tables and lists containing database data (for applications such as parts catalogues);
• merge XML microdocuments from a relational database into "master" XML document (for applications such as user manuals that share feature writeups across a range of products);
• generate large volumes of XML based on simple, repetitive changes in one part of the document;
• generate traditional relational database reports, but within an XML "wrapper" for sophisticated formatting via XSL; and
• generate XML test data.

For example, let's assume that you want to publish a database table cartoon containing cartoon characters and their catchphrases:

To publish this table in an XML file, you first need to create an XML template with processing instructions (shown in bold) that specify how to query the table and where to place the data:

After running ODBC2XML, here's the result:

Defining XML Templates

The input to ODBC2XML is a well-formed XML template file, optionally containing processing instructions that define SQL queries and result field placement rules. All of the text defined in the input file is used to generate the output file; in the absence of any special processing instructions, the input XML file is simply copied to the standard output device.

Defining SQL Queries

The processing instruction to define an SQL query uses the following format:

<?odbc2xml query-name:select-statement?>

where query-name is the name that uniquely identifies a query (one or more alphanumeric characters), and select-statement is the text of the SQL statement to submit to the ODBC database. For example, given a table Products containing a set of product names and descriptions, the following query retrieves all such records:

<?odbc2xml q1:select * from products?>

Query Scope

Each SQL query has a scope in which it is effective, and in which its fields are available for insertion into element text and/or attribute values. This scope is exactly defined by the parse scope of the next XML element to follow the processing instruction. When the query is executed, ODBC2XML processes the elements within scope once for each record in the result set.

For example, given 3 records in the Products table, the following code will produce 3 list items:

<list>
<?odbc2xml q1:select * from products?>
<item>item text</item>
</list>

However, simply moving the processing instruction above the list tag changes the scope of the query such that 3 separate lists will be produced:

<?odbc2xml q1:select * from products?>
<list>
<item>item text</item>
</list>

Inserting Query Results into Character Data

The results of a query can be inserted anywhere within XML element text via the following processing instruction:

<?odbc2xml query-name.field-name?>

where field-name is the name of any field within the ODBC result set. This directive can only be included within the scope of the query.

For example, the following XML template file inserts the ProductName field as a list item within the memo:

<?xml version="1.0"?>
<memo>
<list>
<?odbc2xml q1:select * from products?>
<item><?odbc2xml q1.ProductName?></item>
</list>
</memo>

Placing ODBC data into text which is outside the scope of a query will result in a runtime error:

<?xml version="1.0"?>
<memo>
<list>
<?odbc2xml q1:select * from products?>
<item>item text</item>
</list>
<p>This is wrong: <?odbc2xml q1.ProductName?></p>
</memo>

Inserting Query Results into Attribute Data

ODBC data can also be merged into an element's attribute values. Since XML does not allow a processing instruction to be embedded with an attribute's text, a slightly different method is required. The ODBC2XML program recognises the following pattern within an attribute's value (after all internal entities have been resolved):

<element attr="odbc2xml query-name.field-name">

For example:

<?xml version="1.0"?>
<memo>
<list>
<?odbc2xml q1:select * from products?>
<item productname="odbc2xml q1.ProductName"></item>
</list>
</memo>

Nested Queries

ODBC2XML allows multiple SQL queries to be active at the same time. For example, the following XML template file generates a series of memos, one per product. Within each product, the features of that product are presented in a list. This requires a join between the outermost query (on the products table) and the inner one (on the features table). Within a given query, traditional SQL allows fields to be prefaced with table names as follows:

select * from products,features where features.ProductID = products.ProductID

However, since in ODBC2XML the join is separated amongst two queries (the queries need to have different XML scope), this method is insufficient. Therefore ODBC2XML allows the concept of a foreign field within an SQL query, which is identified to the SQL parser via a leading colon (:) character. This is similar to the method used in SQL precompiler products.

The following example demonstrates the linking of two related queries:

<?xml version="1.0"?>
<memos>
<?odbc2xml q1:select * from products?>
<memo>
<p>The features for the <?odbc2xml q1.ProductName?> product are:
<list>
<?odbc2xml q2:select * from features where ProductID = :q1.ProductID?>
<item><?odbc2xml q2.Feature?></item>
</list>
</memo>
</memos>

Common Problems

ODBC2XML follows the ODBC standard for quoting literal character values and for identifiers. In particular, the MS Access ODBC driver defines these characters to be the single quote (') and backwards quote (`) respectively. You should be aware of these characters if you wish to embed literal string constants into your queries, and/or reference the table names or field names with blanks in them. The following example shows all the use of the different kind of single quotes:

select `Recording Title` from Recordings where `Recording Title` = 'Sweet Home Alabama'

Similarly, the MS Access ODBC driver defines the '#' character to delimit date constants:

select * from Recordings where RecordingDate > #05/10/1999#

Also, when selecting aggregate functions (such as count(*)) and expressions (such as a+b) from record sets, the ODBC driver assigns names to the resulting columns. In particular, the MS Access ODBC Driver uses the convention Expr<N>, where N is a counter, generally beginning at 1000. It is much easier to assign a column alias within the query to avoid guessing at what naming convention the driver is using. To do this, use the ODBC SQL syntax as column-name after the expression in the select list. So to retrieve the value of count(*), use the following example:

<?odbc2xml q1:select count(*) as count from Recordings?>
<list>
<item><?odbc2xml q1.count?>
</list>

ODBC2XML does not do a validating parse; therefore any DTD provided as part of the input XML template file is not processed. Also note that XML does not provide for entity references within processing instructions; therefore entities cannot be used as part of SQL queries.

API Functions (Optional)

ODBC2XML is also provided as a DLL with a simple C-callable API. The intent of the API is to speed up server-side applications, so that each invocation of ODBC2XML need not re-initialize the XML parser, re-connect to the ODBC database, etc. A C header file is provided with prototypes for the API functions (odbc2xml.h). Also, we have included a C program containing example calls to the API functions (demo.c).

To compile the example using MS Visual C++, use the following command:

cl demo.c /link odbc2xml.lib

The ODBC2XML API is currently NOT thread-safe. Applications should make sure that API function calls are made one at a time without re-entrancy. Future versions may address this issue; feel free to contact us with your comments.

Each API function returns an integer error code under the following guidelines: