Step 1: Instrumenting SQL Files with Doc-Comments
Your goal in this step is to embed documentation into your SQL source files. This step probably takes the most time because you
have to do the work (in subsequent steps the computer does the work). As an example, suppose you have a stored procedure unimaginatively named MyProc
, defined in a file by itself, named MyProc.sql
, which takes two parameters.
|Figure 1. Instrumenting a SQL File: Insert XML documentation elements as SQL comments. (The body of the procedure has been omitted for brevity.)|
shows the initial base file on the left (base.sql
) and the first stage of instrumenting it on the right (xhtml.sql
). The highlighted region on the right contains the inserted XML documentation. The inserted text is pure XHTML that describes the procedure—in other words, this is just an XHTML document embedded within a SQL document. Each XHTML line is preceded with a SQL comment marker. SQL Server will interpret the file as pure SQL because it ignores comments, but the XML extractor application does just the opposite: it ignores the SQL and any plain comments, and interprets only those comments containing the documentation. Comment position is irrelevant; you may put all the documentation comments into a single section, as in Figure 1
, or interleave them with the code, creating multiple comment sections.
The XML extractor application (sqldoc2xml) reads each line of text. It recognizes the start of an embedded XML documentation section by the presence of a SQL comment marker (—) followed by an opening bracket for an XML tag (<). Any whitespace between the two tokens is ignored. After finding an XML comment, sqldoc2xml considers all subsequent, contiguous comment lines to be part of the embedded documentation section as well; however, the documentation section ends when sqldoc2xml encounters a non-comment line. At that point, it again starts ignoring all text until it finds another comment marker and XML opening bracket on a single line. In other words, sqldoc2xml collects all the documentation comment sections, strips off the SQL comment markers, and thus distills a pure XML file while ignoring everything else. It is a simple process, but quite effective.
The next stage is to use custom XML instead of pure XHTML to enhance the maintainability of your code base and leverage the power of the XmlTransform transformation tool. Your task is to extend XHTML with new elements and then provide XSLT rules to map these new elements into standard XHTML. Think of the new elements as XHTML "macros." You might have a macro to specify what an example looks like, what a usage section looks like, what copyright you want to use, and so on—all things that you want to specify in one and only one place—so if you need to change something later, you can simply change the macro definition. After defining your macros, you create XSLT rules that convert them into pure XHTML, which may be rendered in any browser. Finally, with your macros and XSLT rules in hand, you then instrument your SQL files with documentation comments written in your custom XHTML dialect, rather than pure XHTML. This extra initial effort makes your documentation comments both completely customizable and highly maintainable.
The translate.xsl file included in the sample project accompanying this article provides a number of macros and translation rules to get you started, as will be described in the sections that follow.
Define a Return Value for a Function
The <cc:returns> element specifies the type and a description of the return value of a function.
|Author's Note: By design, the <cc:returns> element and other documentation elements mirror similarly named C# documentation elements, although they may add or omit attributes.
Table 1 describes the details of this element; the same format is used for each subsequent element. The table first identifies the XML element used in documentation comments (prefaced by SQL comment characters in your code). The element is shown as a template, with the parts that you must fill in indicated in blue.
The XML schema representation presents a more formal and precise representation of the same information; it shows a portion of the formal schema for the custom extension of XHTML discussed in this article. The cc
namespace of <cc:returns>
is implicit in the diagram, hence it appears as returns
in the blue box. Conversely, the XHTML elements that do not use namespace prefixes in the files in the sample project are shown with an xhtml
prefix in the diagram. In words, this diagram states that the <cc:returns>
element has a type
attribute, and its body consists of any number of standard XHTML block
|Author's Note: XmlTransform can use the schema file behind this diagram to validate your input files; that is, XmlTransform can tell you whether your files use the correct XML grammar. Although schema validation is beyond the scope of this article, see the XmlTransform API for information on how to invoke the validation.
The next row of the table shows the XSLT rule that operates on the XML element. When your file is being converted from XML to XHTML and the given XML element is encountered, the rule specifies how to map that element to XHTML.
The remaining rows show a sample, starting with how the element would appear in a SQL file, the intermediate output from sqldoc2xml
, and the final output from XmlTransform. In most cases, moving from the SQL input to the intermediate XML is trivial; the process just removes the leading SQL comment markers. But the next element, <cc:param>
, shows one instance where additional work is performed.
Table 1: XHTML Macros: The <cc:returns> element functions as a documentation macro tag that outputs the return type of a function.
|XML schema representation
<span style="font-style:italic; color:green">
|SQL input sample
||—<cc:returns type="table[data as varchar(256)]">
—This table-valued function processes blah, blah, blah...
|Intermediate XML sample
||<cc:returns type="table[data as varchar(256)]">
This table-valued function processes blah, blah, blah...
|XHTML output sample
(see Figure 2, frame 1)
<span style="font-style:italic; color:green">
table[data as varchar(256)]
This table-valued function processes
|Figure 2. Page Components Revealed: Each customized element maps to a standard XHTML representation.|
The final row in the table for each custom XML element includes a reference to a frame in Figure 2
, and each frame shows rendered XHTML for a particular macro tag in context.
In Figure 2
, the numbered sections correspond to these macro tags: