SQLISAPI and XML Client-Side Processing (XML and SQL part 10)

Throughout the most recent articles of this series, we have been covering a range of new features introduced in SQLXML 2.0. We are continuing this trend by presenting the client-side XML processing.

Let's start by defining what this term signifies. In its typical configuration (original version included with SQL Server 2000 and default configuration with newer versions), the SQLXML component located on the IIS server sends a request to SQL 2000 server, which processes it, generates a standard rowset, transforms it into XML format, and returns results back to the Web server. This places responsibility for conversion between relational and XML formatted data on the SQL server. In a multi-tier environment, where clients access a database via a Web server farm, it is frequently beneficial to shift some of the processing (in particular, the transformation to XML format) to the tier where redundant, load balanced Web servers reside. This is where client-side XML processing comes into play. Note that the reference to "client" is a bit of misnomer, since the XML processing in this case takes place on the side of the Web server (hosting SQLXML component), which, in turn, services clients requests.

There are essentially two ways to implement XML Client-Side processing (all require SQLXML 2.0 or later):

• using SQLASAPI Virtual directory "Run on the client" property, in combination with URL queries, mapping schemas, or template queries,

• using templates with "client-side-xml" attribute set.

It is also possible to accomplish the same goal by applying the programming method available in the ActiveX Data Object model and .NET managed classes implemented in SQLXMLOLEDB provider, however our focus is on a non-programmatic approach. If you are interested in the details of such solutions, refer to ADO and SqlXmlCommand Object documentation available on the Microsoft Web Site.

One important factor you need to keep in mind when dealing with client-side XML queries is that they have some syntactical peculiarities (for a review of server-side FOR XML queries you can refer to the first article of this series). More specifically, while the mode of the FOR XML statement cannot be set to AUTO, you can, however, in addition to RAW and EXPLICIT, also use NESTED mode (and combine them with ELEMENTS, XMLDATA and BINARY BASE64 keywords). RAW and EXPLICIT modes are practically identical in both cases, while NESTED is an equivalent to AUTO with several minor differences, documented on the Microsoft Web site and in the SQLXML documentation included with the newer versions. Note that the NESTED mode is intended strictly for the client-side XML processing, so you won't be able to execute it directly against SQL Server 2000, using standard query tools (such as Query Analyzer).

Client-Side Processing with SQLXML 2.0 (and later) Virtual Directory Properties

With newer versions of SQLXML (2.0 or later), the Virtual Directory Properties dialog box (in IIS Virtual Directory Management tool) has an additional checkbox on the Settings tab labeled "Run on the client." By selecting it, you effectively force the FOR XML queries (in the correct mode, according to the rules described above) to be processed in the client-side fashion. Let's consider an example using the Shippers table from the Northwind database. First, create the Northwind virtual directory using IIS Virtual Directory Management (for steps required to accomplish this, refer to the fourth article of this series). Next, select the checkbox next to "Allow sql=... or template=... URL queries," (as well as the one next to "Run on the client" label), on the Settings tab of the Northwind Virtual Directory Properties dialog box. Finally, from a client workstation, type in the following on the client browser window:

http://WebServer/Northwind?sql=SELECT+*+FROM+SHIPPERS+FOR+XML+NESTED&root=ShippersList

where WebServer is the name of the Web server hosting the SQLXML component. This should return the following in the Web browser window:

<?xml version="1.0" encoding="utf-8" ?> 
  <ShippersList>
      <SHIPPERS ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> 
      <SHIPPERS ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" /> 
      <SHIPPERS ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" /> 
  </ShippersList>

In order to verify that the conversion of the rowset to XML format does really take place on the Web server, you can monitor execution of queries via SQL Profiler and filter TSQL SQL:BatchCompleted events based on the target database, ApplicationName (Internet Information Services), or NTUserName (depending on authentication configuration). If you execute the above query, you will notice that the SQL:BatchCompleted Event Class entry contains SELECT * FROM SHIPPERS in the TextData column. If you modify the URL string to:

 http://WebServer/Northwind?sql=SELECT+*+FROM+SHIPPERS+FOR+XML+AUTO&root=ShippersList

and monitor the execution with SQL Profiles, the TextData column will list SELECT * FROM SHIPPERS FOR XML AUTO instead (which confirms that FOR XML query in AUTO mode executed in the server-side fashion).

Similarly, by creating a web page in the following format:

  <FORM ACTION="http://localhost/Nwind" METHOD="POST">
  <B>Enter Shipper ID</B><INPUT TYPE="text" NAME="ShipperID">
  <INPUT TYPE="hidden" NAME="contenttype" VALUE="text/xml">
  <INPUT TYPE="hidden" NAME="sql" VALUE="SELECT * FROM Shippers WHERE ShipperID = ? FOR XML NESTED">
  <INPUT TYPE="hidden" NAME="root" VALUE="ShippersList">
  <INPUT TYPE="SUBMIT" VALUE="Submit SQL Query">

saving it on a web site, and selecting the "Allow POST" checkbox on the familiar Settings page of the Northwind Properties dialog box, you can execute client-side queries based on POST method (by accessing this web page from the browser). One of my earlier articles describes "Allow POST" option in more details.

You can also create an XML template and enable the "Allow template queries" option on the Settings tab. In addition, you will need to create a template virtual name using the Virtual Names tab within the same dialog box. Let's assume that this virtual name will be Templates and will be assigned to the folder where our template has been saved in as ShippersTemplate.xlm with the following content:

<?xml version="1.0" ?> 
<ShippersList>
   <sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql">
           SELECT *
		   FROM Shippers
		   FOR XML NESTED
   </sql:query>
</ShippersList>

Typing the following string in the URL string text box of the browser:

http://WebServer/NorthWind/Templates/ShippersTemplate.xml

will return content of the Shippers table (assuming that the "Run on the client" checkbox has been selected).

Client-Side Processing of Templates with "client-side-xml" Attribute

As an alternative to using the "Run on the client" checkbox, you can also modify a template directly by adding a client-side-xml attribute and setting its value to 1. Continuing with our previous example, this would result in the following format:

<?xml version="1.0" ?> 
<ShippersList>
   <sql:query xmlns:sql="urn:schemas-microsoft-com:xml-sql"
          client-side-xml="1">
		  SELECT *
		  FROM Shippers
		  FOR XML NESTED
   </sql:query>
</ShippersList>

Note that this template will return proper results even if the "Run on the client" option is deselected. As a matter of fact, the "client-side-xml" attribute takes precedence over the setting within the virtual directory properties dialog box (which also means that setting its value to "0" will force the server-side processing of XML queries).

As you can see, XML client-side processing is relatively straightforward to implement, providing that you have a decent understanding of server-side processing methods. In addition, it is fairly easy to modify your existing solution, based on the original version of SQLXML, if you notice SQL server performance issues resulting from processing XML queries.

In the next article, we will conclude our discussion of the XML in SQL Server 2000 by presenting ways to publish stored procedures and templates as Web Services.

» See All Articles by Columnist Marcin Policht