SQL Server Reference Guide

SQL Server I/O: Using HTTP with SQL Server XML Templates

Last updated Mar 28, 2003.

Security is an important part of any computing application. There is a never-ending balancing act between allowing valid users into an account while preventing invalid users from accessing the data.

This balancing act is further complicated when multiple interfaces exist for the data the application stores. SQL Server has many data entry points, and securing them all is a constant task.

One of these methods is Web services for SQL Server. Using this access method, you can allow direct T-SQL access into your system. When users have this kind of access, it's just like allowing them to install Query Analyzer on their desktops. Anything they can type and run there translates to the same access in the URL bar.

If you still want to allow access to the server using the HTPP protocol, but you'd like to enforce higher security, you can create T-SQL inside an XML file that the users run. They will receive the results of the query but won't see the query itself – nor will they be able to type a query in the URL address. This XML file is called a template.

In addition to the template, you can format the XML output into HTML or even text using a style sheet. The XML style sheet (XSL) is another XML document that specifies the format and placement of the data as elements and attributes. This is known as an XML Transform.

The final element in this process lives within the XSL document. The XSL document contains not only HTTP tags for formatting and placement, but also query elements to work with the data itself. The format for these queries is called XPath, a query language much like T-SQL. Learning the entire XPath syntax and structure is beyond the scope of this article, but I will show you enough elements of it to get you started. You can read more about the XPath specification in the links I provide in references section at the end of this article.

To summarize, the elements of this process are a template document which contains an SQL query, an XML document that it returns, a reference within the XML document to a style sheet (transform), and the style sheet that forms the end result. The SQL query creates a data set that XML uses, and the style sheet contains an XPath query that is able to trim or rearrange the data even further.

To begin, ensure that your server is ready for HTTP and SQL Server interaction. If you haven't set up your SQL Server and Web Server to work with XML, check out the last article in this series to see how to install the necessary prerequisites. The only difference in the process for using templates is that you do not need to check the boxes in the virtual site that allow URL queries.

Next, you need to create an XML file in the "template" directory of your SQL-enabled IIS virtual directory. Mine is located here:

C:\Inetpub\wwwroot\pubsdemo\template

The file contains three parts: a special namespace declaration, a tag representing a query, and the T-SQL for the query. I've explained tags before, and you know what a query is, but the concept of namespaces might be new to you.

A namespace is an XML attribute that serves two main purposes. The first purpose is that it provides a means of scope – meaning that a tag within one namespace is different than a similar tag in another namespace. The second purpose of namespaces is to provide instructions to an engine to treat the tags in a special way.

Here's a sample XML template from my system, called authors.xml:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT au_lname, au_fname
FROM authors
FOR XML AUTO
</sql:query>
</ROOT> 

The outer element tag, marked ROOT, contains an attribute called xmlns. That starts a namespace, and what follows is the Microsoft-reserved keywords indicating a template query.

The element tag <sql:query> is recognized by the SQL-IIS layer (because of the namespace) and encloses the T-SQL that returns XML to the IIS engine. The tags are all closed out to create a well-formed XML document.

Entering http://localhost/pubsdemo/template/authors.xml in my browser returns this result:

- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <authors au_lname="Bennet" au_fname="Abraham" /> 
 <authors au_lname="Blotchet-Halls" au_fname="Reginald" /> 
 <authors au_lname="Carson" au_fname="Cheryl" /> 
 <authors au_lname="DeFrance" au_fname="Michel" /> 
 <authors au_lname="del Castillo" au_fname="Innes" /> 
 <authors au_lname="Dull" au_fname="Ann" /> 
 <authors au_lname="Green" au_fname="Marjorie" /> 
 <authors au_lname="Greene" au_fname="Morningstar" /> 
 <authors au_lname="Gringlesby" au_fname="Burt" /> 
 <authors au_lname="Hunter" au_fname="Sheryl" /> 
 <authors au_lname="Karsen" au_fname="Livia" /> 
 <authors au_lname="Locksley" au_fname="Charlene" /> 
 <authors au_lname="MacFeather" au_fname="Stearns" /> 
 <authors au_lname="McBadden" au_fname="Heather" /> 
 <authors au_lname="O'Leary" au_fname="Michael" /> 
 <authors au_lname="Panteley" au_fname="Sylvia" /> 
 <authors au_lname="Ringer" au_fname="Albert" /> 
 <authors au_lname="Ringer" au_fname="Anne" /> 
 <authors au_lname="Smith" au_fname="Meander" /> 
 <authors au_lname="Straight" au_fname="Dean" /> 
 <authors au_lname="Stringer" au_fname="Dirk" /> 
 <authors au_lname="White" au_fname="Johnson" /> 
 <authors au_lname="Yokomoto" au_fname="Akiko" /> 
</ROOT>

Although the template file contains the commands to process, the user receives only the result of the query, as shown here.

Now I take that return and use it within a style sheet. Before I do that, I need to make one change to the result. Since the file is rendered on the fly, I need to also add a directive to point to the style sheet I want. I change the authors.xml file to two new lines at the beginning:

<?xml version="1.0" ?>
<?xml-stylesheet type="text/xsl" href="authors.xsl"?>
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT au_lname, au_fname
FROM authors
FOR XML AUTO
</sql:query>
</ROOT>

The xml-stylesheet directive in Line 2 points to a text file with the name authors.xsl in the same directory. This instructs the file to be processed using this style sheet, instead of the built-in style sheet most browsers carry.

The style sheet looks like this:

<?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:template match="authors">
  <HTML>
    <table>
      <tr>
      <xsl:for-each select="*"/>
        <td><xsl:value-of select="@au_lname"/> , </td>
      <td><xsl:value-of select="@au_fname"/></td>
      </tr>
    </table>
  </HTML>
</xsl:template>
</xsl:stylesheet>

The second line of this file contains the namespace that the XML engine understands as style tag directives. Those directives make up the bulk of the tags in this file, but you'll see regular HTML tags mixed in.

The third line tells the browser to parse the return as HTML, as opposed to text or some other format.

The fourth line seeks a match for the node called authors. I can get away with that here, since there is only one node by that name, but if there was another further down on the tree, I'd need to qualify the placement by using the appropriate hierarchy names separated by forward slashes: authors/employs/authors. This is where part of the XPath navigation happens.

Next, I start the HTML tags, with an <HTML>, a <table>, and then a table row tag <tr>. Just below that I use a stylesheet namespace command called xsl:for-each, creating a loop, with an attribute called select. Within the parameters for that select, I use another XPath construct to ask for all members of the current node (the asterisk).

For each column within that row, I use yet another command called xsl:value-of with a select attribute of the attributes themselves – in this case, au_lname and au_fname. I specify that this is an attribute instead of an element by using an "at" sign in front of those names. I'd leave that sign out if those were the values of elements instead of attributes.

All that's left now is to close out all the tags. I save that file, call the original XML file in my browser, (http://localhost/pubsdemo/template/authors.xml) which refers to the stylesheet, which creates the HTML.

The output created by the stylesheet now has this format:

Bennet , Abraham 
Blotchet-Halls , Reginald 
Carson , Cheryl 
DeFrance , Michel 
del Castillo , Innes 
Dull , Ann 
Green , Marjorie 
Greene , Morningstar 
Gringlesby , Burt 
Hunter , Sheryl 
Karsen , Livia 
Locksley , Charlene 
MacFeather , Stearns 
McBadden , Heather 
O'Leary , Michael 
Panteley , Sylvia 
Ringer , Albert 
Ringer , Anne 
Smith , Meander 
Straight , Dean 
Stringer , Dirk 
White , Johnson 
Yokomoto , Akiko

There is an entire series of articles you should now read on stylesheets and XPath. Make sure you check the references.

Online Resources

One of the best tutorials on XPath that I've run across can be found here.

InformIT Tutorials and Sample Chapters

Nicholas Chase has a tutorial of XML stylesheets here. He also explains XPath.