SQL Server queries can be returned as a valid XML as well as a rowset (by default), there are multiple ways you could control the format the end result XML is returned in, giving SQL developers flexibility that can meet most of the demand of an application consuming this XML data. XML support has been natively built into SQL Server since the 2005 release.

In this post I will be briefly exploring the different type of XML output modes supported by SQL Server through the FOR XML statement.

SQL Server FOR XML Clause

FOR XML is a clause used at the end of a SQL SELECT statement that tells the engine the results should be returned as XML, a simple example of using FOR XML:

This essentially uses one of the FOR XML flavors (or modes) called AUTO to return the name and object_id as attributes in a tag called sqlobject from the msdb.sys.all_objects DMV. The result of the above SELECT statement can be seen below, notice the lack of a root element, which we will discuss later.

FOR XML supports 4 different modes that gives you the ability to control how the end result XML is formatted.

FOR XML AUTO

the AUTO mode of the FOR XML clause provide the simplest and least controllable (in terms of formatting) XML output, the AUTO mode has the following properties:

• Each table, if the table has a column in the SELECT statement, is returned as an XML tag with the table name (or alias) as the tag element name.
• Each column in the SELECT statement is represented by default as an attribute on the table tag, of the format <[table-name] [column_name]=”[value]” …/>
• If FOR XML AUTO, ELEMENTS is used, columns are represented by tags nested under the table’s tag element, instead of attributes on the table tag element.
• XML Hierarchy (nested tags) between joined tables is conveyed by the order of the columns in the SELECT statement; which determines the nesting of the tags, and the ORDER of the rows in the returned result set; which determines the grouping of the nesting (if the column is not a primary key), hence it might be important to take the ORDER of the returned rows into consideration. If the column is a primary key then the order is ignored and tags are grouped based on the value of the primary key.
• Aggregated columns (using SUM, MAX, etc.) are placed on the deepest nesting level, unless they are specified at the start of the SELECT statement, in which case they are added to the top tag as either an attribute (by default) or a tag (if ELEMENTS is used).

FOR XML RAW

The RAW flavor of the FOR XML clause allows you to get quite  a literal mapping of each row in the SQL result set to a <row> tag within the outputted XML.

• Each row is returned as a tag called row, the tag name can be overridden by passing the name as a parameter to the RAW clause, for example: FOR XML RAW(‘newrow’)
• Each column is returned as an attribute on the row tag, or as a tag in its own right nested under the row tag when the ELEMENTS directive is used.

FOR XML EXPLICIT

FOR XML EXPLICIT provides the most comprehensive control over how your XML is actually returned in terms of nesting and formatting, but sacrificing complexity in the process.

• With extra control comes the responsibility of ensuring the XML is well-formed and valid.
• Hierarchy is established using two columns that must exist on the table, these are Tag and Parent, which determine explicitly how the nesting occurs. A value of NULL or 0 in the Parent column indicate that the Tag has no parent.
• Each column name explicitly states the following: ElementName!TagNumber!AttributeName!Directive. The Directive is optional and allows you to specify additional XML information, and can contain values such as: id, idref, hide, element, elementxsinil, xml, xmltext and cdata.

Continuing from the example above, the query will need to be shaped like below in order to format the results correctly for the EXPLICIT mode of the FOR XML clause.

The above will expectidly yield the following XML

FOR XML PATH

The PATH mode falls in the middle between the RAW and EXPLICIT mode, in terms of complexity and formatting capabilities, in terms of default behavior, PATH behaves very similar to RAW combined with the ELEMENTS directive, but it allows a richer vocabulary to control the output, such as:

• If a column name starts with an unescaped @ sign then the column is added as an attribute of the <row> tag, rather than an elements of its own under the <row> tag.
• If the column doesn’t start with @ and contains a Slash Mark / then each Slash Mark indicates a hierarchy.

An example of using FOR XML PATH should help clarify its descriptiveness as well as its simplicity

Will return the following xml result set:

FOR XML Syntax and Behavior

• The text, ntext, image, or xml data-types are non-comparable when used in the context of the FOR XML clause, hence these are always assumed different (even if the objects are exactly the same), this could lead to nesting inconsistencies. (n)varchar(max) and varbinary(max) should be used if value comparison is a requirement.
• ROOT(‘[root-name]‘) option of the FOR XML clause: Allows you to define the root element of an XML result
• TYPE option of the FOR XML clause: Returns the results as type xml, useful when assigning the results to a variable
• Null can be displayed as xsi:nil=”True” XSINIL directive is used along with the ELEMENTS one in the RAW, AUTO and PATH modes.

Microsoft’s MSDN has a comprehensive overview of all supported options and the basic syntax of the FOR XML clause.

SQL Server Returning XML Results rated 4 out of 5 by 1 readers