Retrieving Data in XML Format—Continued

Now that you know you can communicate with SQL Server over HTTP, let's look at the remaining ways in which you can query SQL Server from across an intranet or the Internet and receive results formatted in XML.

In certain cases, you can query SQL Server and receive results as an unformatted stream of concatenated strings instead of XML. You can read more about this option in SQL Server Books Online (available for download on Microsoft's site—see http://www.microsoft.com/sql/techinfo/productdoc/ 2000/).

SQL Queries in URLs

You can directly issue SQL queries in the query string of a URL. The URL must point to the virtual directory that points to SQL Server discussed earlier. You must specify the FOR XML clause in the query if you want the results to be returned as XML.

Using a browser that is capable of interpreting and rendering XML, such as Internet Explorer 5 and above, you can directly view the XML results of queries sent by way of a URL in the browser. Previewing results in a browser is an easy way to test and debug the connection to a database.

URLs must be formatted according to a particular syntax so that Web servers know how to parse their contents. This syntax includes requirements to encode spaces and other special characters with other characters that are legal in a URL. This is known as URL encoding. A space, for example, must be encoded as a plus sign (+) or its ASCII equivalent value in hexadecimal. For instance, suppose you wanted to send the following query in a URL:

SELECT ContactName FROM Customers FOR XML AUTO

Look at how this query would be encoded when sent in the query string of a URL. In this example, the URL refers to a SQL Server that has a registered virtual directory mypath on the IIS server named my server.

http://myserver/mypath?sql=SELECT+ContactName+FROM+Customers+FOR+XML+AUTO

Alternately, the URL can be expressed with illegal values encoded in hexadecimal notation. With this method, illegal characters are converted into their ASCII value, in hexadecimal, and the percent sign (%) is added as a prefix. Using this notation, the same query looks like the following:

http://localhost/nwind?sql=SELECT%20ContactName%20FROM%20Customers%20FOR%20XML%20AUTO

In order to try this feature and preview the results in Internet Explorer, you'll need to enclose the results in a root element; otherwise, Internet Explorer reports an error. You can do this by passing another parameter to the URL query string specifying the name of the root element. Parameters in URL query strings are sent as name/value pairs. In URLs, name/value pairs are separated by an ampersand character (&). Altering the preceding query, the proper form of the URL for previewing in Internet Explorer is the following:

http://myserver/mypath?sql=SELECT+ContactName+FROM+Customers+FOR+XML+AUTO&root=ROOT

You don't have to name your root element “ROOT.” You can call it Ray, or you can call it Jay, or you can even call it RayJay if you want (no spaces).

Encoding lengthy or complex queries, such as queries that contain subqueries, can be cumbersome, as you can see. Fortunately, less awkward alternatives actually use XML to format queries sent to SQL Server.

A word of caution regarding this feature is warranted. Opening your server to the Internet (or even an intranet) and enabling any user who can find your server to execute any query is a dangerous power to enable. The SQL Server configuration tool for configuring the SQL Server virtual directory provides security settings for enabling or disabling URL queries. You should consider disabling URL queries on production systems, given the immense security risk. On development systems, the feature can be useful for testing and debugging.

Template Files

Templates are XML representations of queries stored in a file. You can also call stored procedures from a template. Templates can be sent in URLs in a fashion similar to sending SQL queries in URLs, or templates can be stored in files on the same Web server as the virtual directory that points to SQL Server. Template queries can be executed simply by referencing the name of the template file and the name of the special subdirectory where all templates are held in the URL. For example, the following template query executes the query stored in the file MyTemplate.xml:

http://myserver/mypath/templates/MyTemplate.xml

Using template files is one way to avoid the hassle of URL encoding your queries. Even though queries are predefined in the template file, the template may specify parameters, and values may be passed in the calling URL for those parameters. The syntax for defining template queries is relatively simple, and the schema is defined in the documentation that ships with SQL Server. An example will demonstrate the basic idea.

Suppose I want to issue the following SQL query against the Northwind database that ships with SQL Server and receive XML results in return.

SELECT ContactName
FROM Customers
WHERE Country=@Country
FOR XML Auto

I construct the following template file and format my query in XML as shown:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:header>
      <sql:param name="Country">Germany</sql:param>
   </sql:header>

   <sql:query>
         SELECT ContactName
         FROM Customers
         WHERE Country=@Country
         FOR XML Auto
   </sql:query>
</ROOT>

Then I save this file in a subdirectory of the special virtual directory that points to SQL Server. The subdirectory must be one I've configured to hold templates.

The XML schema for templates is fairly simple and is fully defined in the SQL Server Books Online documentation. One nice thing about XML is that it is self-documenting. You should have no trouble reading this code and figuring out what the various pieces represent. One thing that might not be obvious is the data between the starting and ending sql:param tags. This data represents the default value for the parameter, Country, if no parameter is specified by the caller. Queries with parameters do not have to specify a default value; although if it is unspecified, the query fails when the caller does not specify a value for the parameter in the call.

To fire this query and get the results, I simply type the following URL into Internet Explorer (version 5 or above):

http://localhost/nwind/template/select1.xml

This returns the names of all customers from Germany because I did not specify a value for the Country parameter in the URL. I can specify a parameter to the query by simply adding a name/value pair to the URL where the name is the same name as the parameter defined in the template file:

http://localhost/nwind/template/select1.xml?Country=USA

The results are returned to Internet Explorer, which formats them nicely in the content pane of the browser as shown in Figure 8-2.

Template files are more secure than SQL queries sent by means of a URL in at least two ways. First, since you predefine queries in a file and these files reside on the Web server, users cannot alter the query other than by specifying parameter values. Second, you can configure Web server security settings and specify different permissions on individual templates so that you can control access to queries.

As mentioned previously, you can also directly specify templates in a URL much as you can specify SQL queries in a URL. However, if you think SQL queries are tough to enter in the query string of a URL, try specifying an XML document in a URL. This method does not use a file on the Web server and suffers the same security weaknesses as sending SQL queries in a URL.

XPath Queries

XPath, the XML query language introduced in a sidebar in Chapter 7, is an alternative way to query SQL Server databases. XPath queries can be submitted in URLs directly, or they can be specified in template files. However, unlike SQL queries, XPath queries require an additional element called an XML view, which I'll talk about shortly.

HTTP Post Queries

In addition to sending queries using a URL, SQL and XPath queries can also be submitted to the server using the HTTP POST method of submission. POST is the method typically used to submit HTML forms to a Web server. In fact, you can submit queries using HTML forms and add input fields that you can provide so users can enter values for parameters to the query. What you submit to the Web server is a template query, either SQL or XPath, and any pa-rameter values required. This method of submission also allows client applications to submit any query they choose, so again you should limit access to this feature or deny access entirely.

XML Views

XML Views are XML representations of part of a database schema created for the purpose of exposing a portion of the database to access by XPath queries. They are defined using the XML Data–Reduced (XDR) language, which is an alternative to DTDs for defining XML document schemas. It is based on the XML Data specification, submitted to the W3C in 1998, which is one of many languages that have been proposed to the W3C as an alternative to DTDs. However, at the time of this writing, the W3C moved ever closer to recommending a different alternative language, called XML Schema, as the “standard” alternative to DTDs. Microsoft helped develop and propose the XML Data language, on which XDR is based. However, Microsoft chose to come to market quickly with a solution that worked today rather than waiting for a standards body to approve a new standard language tomorrow.

But XML views are more than just document schemas defined in XDR. XDR schemas simply define the structure of a document. They do not explicitly map elements and attributes in the document to specific database objects, such as tables and columns. XML views require additional information in order to define the mapping between the XDR schema and the relational schema.

Microsoft provides additional attributes for use in annotating XDR schemas with mapping information that describes how the elements in an XDR schema relate to elements in a relational schema. Microsoft refers to these schemas as annotated XDR schemas, which is also known as an XML view. The annotations are really just XML attributes that can be applied to various elements defined in an XDR schema. For instance, you can add an attribute or annotate an element definition to note that the element represents a table in a database. This kind of annotation may be used to help an application that is reading an XML document based on the annotated schema handle the various elements by knowing more about what those elements represent. For example, some elements may be annotated to indicate that they represent tables in the database. Other elements may be marked as columns. Additional annotations, such as ones marking columns as primary key columns, are also available.

As mentioned earlier, one way to retrieve SQL Server data in XML format across HTTP is to define XML views. You do so by defining annotated XDR schemas, storing those schemas on the Web server, and then passing XPath queries against those XML views. You can send the query through the URL, via HTTP POST or by using a template file. All of the features of XDR provide a way in XDR to define a rich schema for representing relational database elements in your XML views.

By now, you might be asking yourself why in the world you'd want to go to all of this trouble to query a relational database with XPath when you could use good, old SQL instead. The answer to this question is simple. If your application requirements can be satisfied by good, old SQL, then by all means just use SQL. The XML features of SQL Server 2000 aren't intended to be generic alternatives to SQL for retrieving data; they are intended for use in applications that require XML output or that can benefit from using XML and related technologies.

The major reason to use XML views and XPath queries is that it is often simpler to get XML results in a complex format using XML views than it is using SQL queries with the FOR XML EXPLICIT option. FOR XML EXPLICIT gives you a great deal of control over the format of the returned results, but it can be very cumbersome to use. XML views provide a more structured and reusable way to specify the format of your XML results.

Another reason to use XML views and XPath may be to support code reuse. For example, suppose that you already have an application that uses XPath to query a corpus of XML documents. Perhaps as your application grows, you find that you must integrate data from some corporate databases into your results. Or you might find that you could benefit from partial decomposition by storing some information in a relational database. In such cases, you wouldn't want to rewrite drastically your XPath-based applications to work with a relational database. The alternative is to retrieve some of your data using SQL and some of your data using XPath and trying to marry them in some way. I can tell you from experience that using disparate technologies to retrieve data and attempting to marry them into a unified view is not a pleasant exercise. By defining XML views on your relational data, you can leverage your existing XPath code to query the relational data using the same technologies, and perhaps even some of the same code, as you use to query your XML documents.^[1]

^[1] At the time of this writing, XPath is the only XML query language supported by SQL Server. For examples of the XPath syntax, see the sidebar XPath in Chapter 7.