Optimizing XPath Queries

Two of XML's biggest strengths are its abilities to represent arbitrary types of data (e.g., purchase orders, financial transactions, calendar items) and to provide a platform-neutral format for exchanging data between business partners. Once you've decided to exchange XML data with your business partner, you need to agree on the XML grammar you'll use to exchange the data. You'll either invent your own custom XML grammar or adopt a predefined, industry-standard grammar such as the XML Common Business Library, xCBL (see http://www.xcbl.org for details about xCBL), if one is relevant to your application. In most cases, you'll build or obtain the XML Schema Definition (XSD) schema that defines the grammar you've created or selected. Although it's not a strict requirement, a schema provides a formal agreement between you and your business partner on the format of the exchanged data.

After you agree on the grammar, you need to build your application to produce and consume the XML data that grammar represents. If you're using SQL Server 2000 or a SQLXML Web release to implement your application, two related technologies—XPath queries against XML Views and FOR XML EXPLICIT T-SQL queries—are best suited to producing a specific XML grammar. Both technologies let you define the exact grammar of the XML query result that SQL Server returns. The main difference between the two is the programming models they use. XPath queries implement an XML-centric model, and FOR XML EXPLICIT queries use an SQL-based model. But even though the technologies implement different programming models, they're similar in function because SQLXML processes an XPath query on an XML View into a FOR XML EXPLICIT query that SQL Server executes.

In deciding which technology to use in your application, you need to consider factors such as the need to maximize your application's performance and scalability and the complexity of the query result's XML grammar. (An xCBL purchase order is a good example of a complex grammar.) Although writing an XPath query against an XML View is simpler for complex grammars, you might decide to write a FOR XML EXPLICIT query instead. For example, because SQL Server executes a FOR XML EXPLICIT query no matter which technology you use, you can include the query within a stored procedure and avoid the overhead of dynamically generating a FOR XML EXPLICIT query from the XPath query against the XML View. But you can avoid writing the complicated FOR XML EXPLICIT query if you know the trick I show you here.

XPath queries are processed on your client or middle-tier machine by the SQLXML OLE DB provider that installs with all 3.0 versions of the SQLXML Web release. The provider reads the mapping schema that defines the XML View, then computes the FOR XML EXPLICIT query to return the XML data your XPath query specified. After it constructs the FOR XML EXPLICIT query, the OLE DB provider submits the query to SQL Server for execution and returns the XML results.

Because SQL Server executes the generated FOR XML EXPLICIT query instead of the XPath query, you can use SQL Server Profiler to view the generated query in a trace. After you capture the query in a trace, you can copy it to your own stored procedure. Because the OLE DB provider replaces any variables in the XPath query with literal values in the generated SQL query, you might have to generalize the query by replacing literal values with parameters you can pass to the stored procedure (assuming that your XPath query includes a predicate).

The following example walks you through each step of this process. I use a simple schema for this example to limit the size of the figures and listings in the article. But the techniques I demonstrate will work for any schema, regardless of complexity.

Before you start, you need to build an XML View by adding annotations to an XSD schema to create a mapping schema that defines how the XML data maps to tables in your database (see the SQLXML 3.0 Docu-mentation for details). This example uses the mapping schema that Figure 1, page 41, shows; it defines an XML View consisting of a list of products from the Northwind sample database's Products table. After creating the mapping schema for the XML View, you need to install the schema in a SQLXML virtual directory so that you can execute an XPath query in Microsoft Internet Explorer (IE). Alternatively, you could write script code that uses ADO and the SQLXML OLE DB provider to execute the XPath query and obtain the XML result. Because I favor solutions in which you can immediately see the XML result, let's use the virtual-directory option.

To create the virtual directory, start the Configure IIS Support utility you installed with the SQLXML Web release. On the Action menu, select New, Virtual Directory. On the General tab, enter Oct2003 as the Virtual Directory Name, and select a local path on your system that's accessible by Internet Information Server (IIS). (Using a directory under Inetpub/wwwroot avoids access errors.) Copy the mapping schema from Figure 1 to a file called Products.xsd in the directory you chose in the previous step. Then on the Security tab, enter the security parameters for accessing your database, and on the Data Source tab, select your SQL Server and the Northwind database. Select the Allow XPath check box on the Settings tab, then go to the Virtual Names tab and create a virtual name called schema with the type schema and the path "." Finally, on the Advanced tab, select all check boxes under Caching options and click OK. Your virtual directory is configured and ready to use.

To execute an XPath query against the XML View that the mapping schema in Figure 1 defines and verify that your configuration is correct, start IE and enter http://localhost/Oct2003/schema/Products.xsd/Products/Product[UnitsInStock<1]?root=Products in the address bar. This URL executes the XPath query /Products/Product [UnitsInStock<1] by using the XML View that was defined by the Products.xsd mapping schema you saved to the virtual directory. The root=Products parameter wraps the query result in a Products tag to guarantee that IE receives a well-formed XML document that it can display. IE will now display the XML document that Figure 2 shows.

After you've successfully executed the XPath query, the next step is to capture the FOR XML EXPLICIT query that the OLE DB provider constructed from your XPath query and sent to SQL Server. Begin by starting Profiler. On the File menu, select New, Trace. In the pop-up dialog box, enter your SQL Server name (or localhost) and your user credentials. Then, click OK to display the Trace Properties window. In this window, leave all the default settings unchanged and click Run to start the trace. The resulting window displays an active trace. If other activity is occurring on your SQL Server, you might see events start to appear.

With the trace started, return to IE and click Refresh or press F5 to execute the XPath query again. You should see events displayed in the Profiler trace, which will look something like Figure 3. Look through the trace events for a SQL:BatchCompleted event that includes the generated FOR XML EXPLICIT query in the TextData column. Click in the BatchCompleted row to display the query text in the lower trace pane. The query should look similar to the one that Listing 1 shows. The column names will be different, but that won't affect the query result. Now copy the query to a text editor.

The final step in the process is to replace any literal values in the XPath query with the parameters you'll pass to your stored procedure. The XPath query uses the predicate [UnitsInStock<1]. If you want to pass the number of required units to the stored procedure, you need to modify the FOR XML EXPLICIT query in Listing 1 to replace the literal value 1.000000000000000 with a parameter from your stored procedure. Listing 2 shows a stored procedure that includes the FOR XML EXPLICIT query in which the literal value has been replaced by the @units parameter. You've now created the equivalent FOR XML EXPLICIT query to generate the XML result that the XML View from Figure 1 defines.

If you're looking for a way to generate custom XML grammars, FOR XML EXPLICIT queries let you leverage SQL Server's query optimization capabilities—at the cost of a complicated programming task. Conversely, XML Views and XPath queries provide an easier programming model for complex schemas but result in less-efficient solutions. But by taking advantage of Profiler's trace function to capture the FOR XML EXPLICIT query corresponding to your XPath query, you don't have to sacrifice time to get the most efficient solution.