f you've ever worked with sophisticated database systems or large applications you've probably accumulated sizable libraries of stored procedure code. Such libraries typically hold a few hundred stored procedures, ranging from perhaps a few hundred lines of code to thousands of lines—but only minimal documentation. That's because existing database documentation generators operate at the database-object level: that is, they describe the database schema, list the table structures, and show object relationships. They do describe stored procedures, but only with a skeleton—typically just a list of parameters and their data types, and then typically include the entire source code. There is no injection of user-supplied documentation from the source file. Clearly, that is not an adequate API.

I have been described as a documentation fiend; I document extensively, from design through development, wherever I go. So I could not, of course, let this situation stand. If these SQL files were C# or Java libraries instead, it would be inconceivable not to have thoroughly documented the code using common documentation generator conventions, so that anyone could generate a complete API describing how to use the code base. For example, look at any C# API in MSDN, and you will find a description of each method and each parameter within each method, as well as sample code usage rather than source code. In C#, NDoc is commonly used for this; Java has JavaDoc, and so on.

To address the problem, this article leverages a Java application I developed some time ago called "XmlTransform," whose purpose is to convert one XML variant to another using XSLT mapping. Originally, the program was intended to generate web pages, but as the ideas behind it are general purpose, XmlTransform serves equally well to generate SQL documentation. To tailor the transformation you must define a few custom XML tags in an XSLT file and instrument your SQL source files with those tags. The tags delineate the various types of content in the final documentation, just like XML documentation used in other languages. Using those tags, XmlTransform then generates a finished HTML documentation set for you.

Author's Note: See the related article, "XmlTransform—A General-Purpose XSLT Pre-Processor," for more in-depth information about using XmlTransform.

The sample project that accompanies this article contains all the necessary code in these directories to demonstrate generating documentation:

• src—SQL source.
• XMLsrc—XML documentation source files extracted from src.
• api—HTML documentation generated from XMLsrc.
• code—programs to generate the SQL documentation set from src into XMLsrc and api (described below).
• sandbox—Component samples you can experiment with.

The code directory contains:

• sqldoc2xml.pl—Perl source code to extract XML documentation comments from the SQL source files in src, putting the results in XMLsrc.
• XmlTransform.java—Java source code for the transformation engine that maps the source XML in XMLsrc into the target XML in api. The source XML is a simple, custom XML dialect that's similar to other documentation languages. The target XML is XHTML.
• translate.xsl—an XSLT file that directs XmlTransform how to map the XML source to the XHTML target.
• cleancode.css—style sheet for displaying the resultant XHTML.
• XmlTransform_params.dat—parameter file for running XmlTransform
• customJar—Java libraries
• lib-external—Perl external libraries
• lib—CleanCode Perl library

You'll find two downloadable zip files in the downloadable code that accompanies this article. The small code archive (175 KB) includes only the collection of sample project files discussed here, along with the output of each technique covered. The larger zip (4.7 MB) is a superset of the first; it also includes all the external Perl and Java library files you need.