Using the sql:max-depth Attribute for Schemas with Recursive Elements

Will XML for SQL Server 2000 Web Release 2 (SQLXML 2.0) support a schema with a recursive element?

Let's define a schema with a recursive element as a schema in which one or more elements contain one or more instances of themselves, either directly or with intervening elements. Recursive elements let you represent relationships between similar items by nesting the XML tags. For example, you could design an organization chart in which each manager's Employee element contains the Employee elements of the staff members who report to that manager. At this time, SQL Server 2000 can't execute recursive queries to build a hierarchical XML output for arbitrarily deep hierarchies, such as the recursive output that Figure 1 shows.

In XML Q&A, May 2001, InstantDoc ID 20036, I described three ways to obtain hierarchical results in similar scenarios. To use a FOR XML EXPLICIT query, you must build a complex query that accounts for each potential nesting level that might occur in the recursive element. You must decide how deep the hierarchy can be, then build a FOR XML EXPLICIT query to produce that hierarchy. The final version of SQLXML 2.0 (http://microsoft.com/sql/downloads) provides a similar mechanism to produce recursive results. But to avoid building large FOR XML EXPLICIT queries, SQLXML 2.0 uses a simpler method, the sql:max-depth annotation, which incorporates mapping schemas and XPath queries. You can use sql:max-depth to specify the maximum number of nested levels that a recursive element in a mapping schema can return. When an XPath query executes using the mapping schema, SQL Server's XML processor generates a query that returns nested elements to the depth that the sql:max-depth annotation specifies.

The XSD mapping schema that Listing 5 shows describes a recursive XML element called Employee. The content model of Employee lets you nest additional Employee elements within a document matching the schema. Figure 1 shows an XML document that matches the schema in Listing 5. The ReportsTo column in the Employees table provides the EmployeeID of the Employee's manager. The mapping schema uses sql:relation to map between the Employee element and the Employees table and uses the sql:relationship tag for the relationship between nested Employee elements. The sql:relationship tag specifies that a nested Employee element in the XML document corresponds to a database record in which the ReportsTo field matches the managing Employee's EmployeeID field. The mapping schema uses sql:max-depth for the maximum number of levels in the hierarchy; in this example, the mapping schema specifies that up to six levels of management exist in the organization. The sql:limit-field annotation on the top-level Employee's xsd:element specifies that the top-level element should have a ReportsTo column equal to NULL (i.e., the sql:limit-field specifies which employee reports to no one). The value NULL comes from the absence of sql:limit-value, which the schema usually includes when it uses sql:limit-field. Using the mapping schema in Listing 5, you can execute the XPath query /Employee, which selects all employees in the virtual document, to obtain a list of Employee tags like those in Figure 1.

To run the /Employee XPath query, you need to install SQLXML 2.0, then use Microsoft IIS Virtual Directory Management for SQL Server 2.0 to configure a new virtual root called Jan2002 (use the Northwind sample database). On the Settings tab for the new virtual root, select the Allow XPath checkbox. Then, on the Virtual Names tab, configure the virtual name schema of type schema with the path "." (meaning the current directory, which is your virtual root directory). The path associates the virtual name with the directory you chose as your virtual directory. Store the mapping schema from Listing 5 to this directory with the filename employee.xsd. The virtual root directory configuration is now complete.

Using Microsoft Internet Explorer (IE) to enter http://localhost/Jan2002/schema/employee.xsd/Employee, the URL uses the mapping schema from Listing 5 to codify a database query. The URL references the virtual root Jan2002, then the virtual name you created (schema), the name of the mapping schema you stored in the virtual root directory, and the XPath query /Employee to complete the query. Your browser runs the query and displays the recursive employee list that Figure 1 shows.

To see how Microsoft implemented schemas with recursive elements, use SQL Server Profiler to trace the query. SQL Server executes a FOR XML EXPLICIT query to get the XML result.