Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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:
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.
One of the best tutorials on XPath that I've run across can be found here.