The Three Modes of SQL Server XML

One promise of the XML standard is that it will provide data streams that are not only platform independent but also vendor (or organization) independent. For example, a healthcare industry standards group is attempting to develop XML message formats that will allow data sharing throughout the industry, regardless of the software application or database from which the data originates. Standards groups in other industries are working on similar standards.

In the case of SQL data structures, one challenge is to extract data and create XML structures that meet these industry-wide standards. Microsoft has provided the ability to use the SQL SELECT statement to retrieve results in XML format, with three SQL Server XML modes—Raw, Auto, and Explicit—that format the resulting data streams. Of these three modes, the last two provide some degree of flexibility, but most likely, only the Explicit mode is sufficiently robust to meet complex standards.

The Raw mode is, as the name implies, raw. It returns the result set with each result row wrapped with the generic identifier of "row." This format might be suitable for some internal applications where data is converted into XML messages that an interface engine will process or manipulate further. However, message exchanges with external organizations will normally require adherence to standards with more descriptive tags.

Auto mode provides control over the tags but little control over the structure. It returns query results in a nested XML tree. Although the default tags consist of the table names and the fields within the tables, you can overwrite these defaults. The easiest way is to specify aliases that map the internal structure to an external standard. The other way to overwrite the defaults is to specify a Document Type Definition (DTD) as part of the query. Either way, the output from Auto mode probably won't produce an XML message that meets the requirements of an industry-wide messaging standard.

That leaves the Explicit mode as the most likely way to meet complex requirements. With Explicit mode, you can specify the shape of the XML tree. However, this mode requires relatively complex SQL queries that must specify the structure of a universal table that has all the information about the desired XML tree. The universal table must include metadata columns—a tag column that stores the tag number of the current element and a parent column that stores the tag number of the parent element. The query also must specify the column names in the universal table. The syntax that Explicit mode requires is rigorous, and the developer assumes responsibility for ensuring that the generated XML is well-formed and valid.

Even with the Explicit mode's flexibility, some messaging requirements might be beyond the ability of direct data extraction using the SELECT statement. For these, you might be better off using simple Raw mode and outputting the result to BizTalk, where you can map the data into complex messages.