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 <\?MSSQLError.*\?> selects a single occurrence of a SQL XML error PI by searching the result for the starting string <\?MSSQLError. The backslashes (\) remove the question mark's (?) special meaning in regular expressions (the question mark matches the previous subexpression) so that the regular expression matches the question mark literally within the string. The period and asterisk characters (.*) following MSSQLError match any arbitrary character string and are used to match the error code, source, and error description that follow the MSSQLError string. Finally, the slash, question mark, and closing bracket characters (\?>) match the end of the PI.