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 instead (which confirms that FOR XML query in AUTO
FOR XML 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.
