Channels ▼
RSS

Database

XML, SQL, and C

Source Code Accompanies This Article. Download It Now.


Jim is a researcher at the University of California Santa Cruz. He can be contacted at kent@soe .ucsc.edu.


XML is an increasingly popular format for exchanging data. It handles optional fields and hierarchical data structures well, is readable by humans as well as computers, is portable across a wide variety of platforms, and it can even handle recursive data structures. In my own field of bioinformatics, XML has become almost as common as the venerable tab-separated file for exchanging information between databases. Still, integrating data from XML sources into our relational databases and our largely C code base sometimes seemed to involve more work than it should. Consequently, I was motivated to write the four tools—autoXml, AutoDtd, sqlToXml, and xmlToSql—presented in this article. The complete source code for the programs and libraries is available electronically from DDJ (see www.ddj.com/code/) and from me (www.soe.ucsc.edu/~kent).

XML files contain structured data, and the C language, of course, can represent structured data as well. However, to get from the XML to C representations is not a simple task. At a minimum, you must write several lines of code for each field of each data structure. Converting the parent/child relationships from XML to C is especially problematic. Using stream-oriented parsers requires that your callback procedures maintain some sort of context stack so that the child can be linked into the parent. With tree-oriented parsers (such as DOM), the relationships are reflected in the parsed tree. Unfortunately, the whole tree must fit in memory, which is not possible with larger databases.

The autoXml program automates mapping between C and XML data structures. Its input is an XML document type definition (DTD), and it outputs the equivalent data structures in C. The code converts these structures to and from XML. The full tree can be loaded, or just subtrees corresponding to a particular tag and its children. The C structure can contain numeric as well as string fields.

AutoDtd creates a DTD from an example XML file. This comes in handy because not all XML files have a DTD (autoXml requires one). AutoDtd figures out whether elements are required or optional; are lists or nonempty lists; and whether the fields are integer, floating point, or string, just by examining the example file.

Another common task is saving data stored in a relational database in XML format. The sqlToXml tool produces descriptive and readable XML guided by a simple tree definition that describes the parent/child relationships within the database. Currently, sqlToXml only works with MySQL, but it shouldn't be too much trouble to adapt it to other relational databases.

Finally, xmlToSql converts an XML document into a reasonably normalized relational database. The output is a directory full of tab-separated files and SQL table creation statements, so it should work with any relational database. The normalized relational representation is typically an order of magnitude smaller than the XML representation of the data.

All four programs are built using the GCC compiler linking in with the jkweb.a library. The programs, which you can incorporate into commercial or open-source tools without licenses, are regularly built on Linux and Mac OS X, and should port with a minimum of effort to other UNIX-like platforms. You'll need to have MySQL installed to build the sqlToXml program.

The programs run from the command line and print a usage summary if run with no parameters. You can use stdin/stdout as filename parameters if you wish to use them as part of a pipeline.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video