XML2ODBC is a Win32 command-line utility that stores data from XML documents into any ODBC database. Its simple syntax provides an direct and easy way to identify data within an XML file, combine it with other SQL data and expressions, and populate ODBC database table rows and columns. Based on the Xalan technology from the Apache XML Project, it uses standard XPath expressions to navigate thru XML files.

You may obtain the XML2ODBC freeware version and use it on a single-user machine for any purpose. However, you may not redistribute this version for commercial use, and attempting to store more than 15 rows into a database will abort with the error "Row limit exceeded". We can also not provide support for this version. 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 online at RegNet.

Overview

Let's assume that you have an XML file you'd like to store into a database table. For example, assume that the database table cartoon will contain cartoon characters and their catchphrases:

Assumes that the data to populate this table is in an XML file named "cartoon.xml":

<?xml version="1.0"?>
<memo>
<from>Me</from>
<to>You</to>
<re>Important Cartoon Characters</re>
<p>Here is a list of cartoon characters and the brilliant things they say:</p>
<characterlist>
<character>
<name>Bart Simpson</name><phrase>Don't have a cow, man</phrase>
</character>
<character>
<name>Homer Simpson</name><phrase>Doh!</phrase>
</character>
<character>
<name>Bugs Bunny</name><phrase>What's up, doc?</phrase>
</character>
<character>
<name>Elmer Fudd</name><phrase>Be verwwwy quiet! I'm hunting wabbits!</phrase>
</character>
</characterlist>
</memo>

You can use XML2ODBC to easily populate the database table. The format of the command line is:

xml2odbc [options] dsn file row-expression sql-expression [row-expression sql-expression...]

First, provide the name of the ODBC data source to connect to, and the name of the XML file to parse. Next, provide an XPath "row-expression" that identifies an XML element that determines the number of rows to be populated. In this case, one row in the table is desired for each cartoon character. A valid XPath expression for this would be:

/memo/characterlist/character

Finally, provide an "sql-expression" that is a template for an SQL INSERT statement that will be executed for every matched row. Within this statement, you can use XPath expressions to identify the particular XML data desired for each column. In particular this template can contain:

• literal values;
• SQL functions and expressions; and
• XPath expressions.

XML2ODBC will replace any XPath expression in an SQL statement template that begins with a colon character (':') with its value from the XML document. These "column" XPath expressions are relative to the row-expression. Since in this example the <name> and <phrase> child elements of the <character> element as being the data sources for the columns, the XPath expressions are simply "name" and "phrase" respectively. Remember to add the additional ":" to tell XML2ODBC to process these as XPath expressions, and to place these within single quotes as SQL will expect character strings:

insert into cartoon values(':name',':phrase')

The complete command-line to store all this data to a table named "cartoon" in an ODBC database with the data source name "mydsn" is:

xml2odbc mydsn cartoon.xml "/memo/characterlist/character" "insert into cartoon values(':name',':phrase')"

After running this command line, the resulting table rows will be as follows:

XML2ODBC can also process multiple row-expression/sql-expression pairs. Here's an example that first drops any existing "cartoon" table (you can execute an SQL statement one time by matching the XML file's root element, identified the "." XPath expression), then creates a new "cartoon" table, and finally populates it with data from the cartoon.xml file:

xml2odbc -c3 -v sample cartoon.xml "." "drop table cartoon" "." "create table cartoon(Name varchar(16),CatchPhrase varchar(32))" "/memo/characterlist/character" "insert into cartoon values(':name',':phrase')"

Since it uses the -v (verbose) flag, executing this command produces the following log to the stdout device:

drop table cartoon
create table cartoon(Name varchar(16),CatchPhrase varchar(32))
insert into cartoon values('Bart Simpson','Don''t have a cow, man')
insert into cartoon values('Homer Simpson','Doh!')
insert into cartoon values('Bugs Bunny','What''s up, doc?')
insert into cartoon values('Elmer Fudd','Be verwwwy quiet! I''m hunting wabbits!')
commit

Obtaining XML2ODBC

XML2ODBC is implemented as a 32-bit Windows executable. It requires Windows 98, NT, ME, or XP, and one or more ODBC drivers (not supplied). The freeware version limits the number of output SQL records to 15; otherwise it is the same as the registered version.

The freeware version is available here: xml2odbc.zip and contains the following files:

• xml2odbc.htm (this documentation file)
• xml2odbc.exe (Win32 command-line executable)
• cartoon.xml (sample XML file)
• demo.bat (demonstration command line for storing cartoon XML into database)
• DOMSupport.dll XMLSupport.dll PlatformSupport.dll XalanDOM.dll xerces-c_1_6_0.dll XercesParserLiaison.dll XalanSourceTree.dll XPath.dll: DLL files from Apache XML Project

Installation

To install XML2ODBC:

• Unpack the distribution ZIP file into a directory on your hard drive (normally c:\xml2odbc).
• Place the name of this directory into your PATH variable.

Usage Instructions

To run XML2ODBC, use the following command line syntax:

xml2odbc [options] dsn file row-expression sql-expression [row-expression sql-expression...]

where:

• options are:
  • -c1 commit each statement upon execution
  • -c2 commit only after each row XPath expression is finished
  • -c3 commit only after entire session is finished (can include multiple row expression/insert statement pairs)
  • -p pwd password for ODBC data source
  • -u uid userid for ODBC data source
  • -v verbose (echo each SQL statement as it is executed)
  • -x build but do not execute SQL statements
  • -? list options
• dsn is the ODBC data source name to connect to
• xmlfile is the name/path of the input XML file
• rowexpr is an XPath expression to be applied to the XML file that identifies each row in the table
• insertstmt is a template for an SQL insert statement, containing XPath expressions where data from the XML file is required

Arguments may be optionally quoted using single or double quotes, as in:

xml2odbc "long data source name" "long filename" "rowexpr" "insertstmt"

Verbose mode output is written to the standard output device and may be redirected using standard Windows file redirection.

Release Notes

• 12-July-2002: Documentation updates
• Version 0.1, 04-July-2002: Web release of beta-test version

Known Limitations:

• Full ODBC connection strings are not supported

Registration & Licensing

You may use the freeware version of XML2ODBC for any period on a single-user machine. You may not distribute copies of this version, there is a 15-record output limit, and we cannot provide support for this version. Registration provides you with the full production version (with no record output limitations), and up to 1 hour of free technical support, and optionally, the ability to redistribute commercially. The following licenses are available:

Sorry, we cannot provide support to unregistered users. Contact us for other site licenses and support plans. You may also register via check or money order. Please include your name, address, phone number, and email contact information when registering:

XML2ODBC and its accompanying documentation are Copyright © 2002 Intelligent Systems Research. All Rights Reserved. This product includes software developed by the Apache Software Foundation (http://www.apache.org/), which is included under the terms of the The Apache Software License, Version 1.1. Your use of the program also indicates acceptance of the License Agreement with Intelligent Systems Research.