Detecting and Processing Errors in XML Queries and Updategrams

Detecting and Processing Errors in XML Queries and Updategrams

How can I detect and display errors that occur during updategram processing?

Let's approach this question by examining the general topic of detecting errors, then looking at an updategram example. When you use SQL Server 2000's XML functionality, SQL Server generates a special XML construct called a processing instruction (PI) to signal an error to the application that processes the XML data. The PI includes the error code, the source of the error, and a description of the error. To see how SQL Server returns errors, let's look at a query embedded in an XML template.

Listing 1 shows an XML template that contains an invalid query to a nonexistent table in the database. Before you execute the template, you need to configure a SQL Server XML (SQL XML) virtual directory named November and create a virtual name. Set the virtual directory to use the Pubs database included with SQL Server, then create a virtual name within the virtual directory called template with '.' as the path. Store the template from Listing 1 to a file named badquery.xml in the directory that you selected for the virtual directory, then execute the query by using the following URL:

http://localhost/November/template/badquery.xml

Figure 1 shows the error PI that the query generates. When you construct templates and updategrams, keep in mind how an error will affect the XML that a query generates, including error PIs. If you want your result in the form of an XML document, you need to ensure that the XML result is well formed. Otherwise, if you attempt to parse the result by using an XML parser, an error will result. The root tag in Figure 1's example ensures that the result is well formed, even though the query results in an error PI.

Now, let's look at three techniques that you can use in a variety of circumstances to detect SQL XML error PIs: parsing the XML into a Document Object Model (DOM) by using XPath or Extensible Stylesheet Language Transformations (XSLT) to search the DOM; using regular expressions; and using the SAX2 parser interface from a C++ program. The method you choose for detecting SQL XML errors depends largely on your application, the processing your application performs, and your application's runtime characteristics (e.g., memory utilization, CPU utilization).

DOM technique that uses an XPath query. Parsing an XML result into a DOM is an easy, straightforward technique that's most appropriate when your application already requires DOM processing. Otherwise, depending on the size of the XML result, use of the DOM could introduce a large memory allocation overhead to your application. After your application has parsed the XML result into a DOM, it can easily search the XML result for PIs by using an XPath query.

Listing 2 shows the DOM technique. The code creates an XMLHTTP object that uses the template in Listing 1 to request the XML result from SQL Server 2000. XMLHTTP is an XML-aware HTTP client that automatically parses an XML result into a DOM. The code obtains the DOM from the XMLHTTP object's responseXML property, then uses the selectNodes method on the DOM object to invoke an XPath search for PIs. The XPath query selects only PIs named MSSQLError, ignoring the presence of other PIs in the document. The code displays a message box that contains all the data for each SQL XML error PI that the XPath query found without accessing the individual error codes, descriptions, and source fields because the DOM doesn't provide an easy way to access data from a PI. (For a code example of how to parse PI data, see XML Q&A, April 2001.) To run Listing 2's code, save its script to a file named XPathSearch.vbs in the directory you used for the virtual directory named November. You need VBScript and the Microsoft XML Parser (MSXML) 3.0 or later installed to run the script. VBScript is included with Microsoft Internet Explorer (IE) 5.0 or later or standalone at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001169. You can download the latest version of the parser at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/topic.asp?url=/msdn-files/028/000/072/topic.xml. If you registered VBScript as executable with the system, invoke XPathSearch.vbs directly from the command line. Otherwise, use the following command, which invokes the code by using the console-based script host:

cscript XPathSearch.vbs

The regular-expressions technique. Regular expressions are patterns that you use to search for specific bodies of text within a document. For example, you can use regular expressions to search the result of a query or updategram for the presence of an error PI. Using regular expressions is most appropriate when the result isn't in XML format or when you want to avoid parsing the XML result. This option generally requires less memory than parsing the XML result into a DOM, but it requires more memory than SAX2 processing because the entire XML result must be stored in a string variable.

Listing 3 shows a code example that uses the regular-expression engine included with VBScript. Like the code for the DOM technique, the code in this example uses XMLHTTP to retrieve the result from SQL Server. Then, the code calls the template (which Listing 4 shows) that generates a text (non-XML) result. The regular expression selects a single occurrence of a SQL XML error PI by searching the result for the starting string ) match the end of the PI.

The next statement sets the Global property on the regular-expression object to True, instructing the regular-expression engine to match all occurrences of the pattern rather than just a single occurrence. The Execute method returns a collection of matching patterns, and a message box displays the data for each PI. To run the code, assign the name badqueryflat.xml to the template from Listing 4 and save the template to the November XML virtual directory you created for the previous example. Then, save the VBScript code from Listing 3 to the same directory with the filename of RegExpSearch.vbs and run the script.

The SAX2 XML parser interface. Using the SAX2 XML parser interface is more efficient than using DOM for detecting errors because SAX2 doesn't generate an in-memory representation of the XML result. This characteristic also makes SAX2 suitable for large data sets. SAX2 is composed of a set of interfaces that supply callback functions to notify your program when the parser encounters different types of XML tokens (e.g., elements, comments, PIs). By using a callback function to catch PI tokens from the parser, your application can search for errors in an XML result.

Web Listing 1 shows a minimalist SAX2 content-handler class. The MyContent class derives from a base class that Microsoft supplies with its "JumpStart for Creating a SAX2 Application with C++" kit at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk30/htm/sax2c_jumpstart_jumpstart.asp?frame=true. The base class implements stub handler methods for all methods on the ISAXContentHandler interface. Using the base class lets you easily implement only the handler methods that you need to complete the task at hand. To detect SQL XML errors, you implement only the processingInstruction method, which prints a message if it encounters a SQL XML error PI.

Web Listing 2 shows the main program that instantiates the SAX2 parser, registers the MyContent handler for callbacks, and invokes the XML parser to parse the document from the URL passed as a command-line parameter. I copied the source code for the main program directly from the JumpStart kit. To run the program, you first need to compile the C++ project. You can download a complete C++ project, including an executable, from the SQL Server Magazine Web site at http://www.sqlmag.com (see "More on the Web" for download instructions). After you compile the program, navigate to the Debug directory beneath the project and run the following command line:

CPPSaxSample http://localhost/November/template/badquery.xml

The SQL XML error PI data appears on the console.

You can use any of these three techniques to detect updategram errors. Let's look at an updategram example that incorporates a technique similar to the DOM technique I described earlier. Listing 5 shows an XML template that uses an XSLT style sheet instead of an XPath query to display error information.

The template consists of a root tag that contains an XSLT declaration and an updategram. Using the root tag ensures that the updategram's result is a well-formed XML document. The XSLT declaration specifies the style sheet that formats the result. The block and its children define the updategram. Note that this updategram always generates an error because the author, Abraham Bennet, already exists in the authors table in the Pubs database. The mapping-schema attribute on the updg:sync tag refers to the mapping schema that Listing 6 shows. The mapping schema maps attributes from the author tag to the authors table in the Pubs database. When SQL Server executes the updategram, two outcomes are possible. If an error occurred, SQL Server replaces the updg:sync tag and all its content with the SQL XML error PI. If the updategram was successful, SQL Server removes the updg:sync tag. For details about updategram processing, see Michael Otey, "XML Updategrams," January 2001. After SQL Server processes the updg:sync tag, the XSLT style sheet processes the resulting XML document. In this example, the XSLT will format an HTML page to display either the error or a message that indicates that errors occurred.

To run the code, store the template under the filename update-authors.xml in the directory that you used in the earlier examples. To the same directory, store the mapping schema from Listing 6 and the XSLT style sheet from Listing 7 with the filenames update-authorsschema.xml and showErrors.xslt, respectively. Invoke the template by using the following URL from IE:

http://localhost/November/template/updateauthors.xml

Figure 2 shows the HTML result that the style sheet from Listing 7 produces. The HTML includes data from two error PIs that SQL Server generates. Note that to retrieve the data from the PI, the style sheet uses the xsl:value-of tag as well as the XPath expression that you used in Listing 2 to select SQL XML error PIs.

Detecting errors isn't difficult when you use SQL Server 2000's XML functionality. Just remember to choose your error-processing technique to match your application scenario.