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
Last updated Mar 28, 2003.
As part of the XML-from-the-database experience, we need to talk about using SQL Server from the Web, natively. The process isn't that involved, and is quite powerful once you implement it.
As always, it's important to keep security in mind when you're developing an application. There are several ways to implement the security: from the database, at the application level, or in the middle-tier. When you write applications that will be availble over the Web, you have to make this decision early, since it is such an open platform. Fortunately, you can use Internet Information Server (IIS) in a Windows-account mode, such that the users are prompted for a login that the server knows about. This can be a local server account or on the domain, whichever works best. You can also place the Web site behind a firewall, so that the users are authenticated before they ever arrive at your SQL Server. Using the integrated security this way makes the authentication process easy.
You could also create a front-end page before this one, and enforce a Web authentication scheme there.
The process for working with XML data from SQL Server is to run the wizard for the web services for SQL Server, create a few directories, set up a virtual directory pointing to them, and then publish query pages to that directory. SQL Server will then respond to queries posed through that Web site, or use template files to display the data. Templates are the better way to go, as they hide a bit of the complexity of the query structure from the users, and also provide a little more security.
Let's break it down, step by step.
First, you need Internet Information Server installed. Test that by hitting a Web page on the server from another system even before you start. I can't tell you the number of times I've had to debug what looked like a complex XML return problem, only to find that the IIS server wasn't working in the first place. This is a special challenge if you've got Windows Server 2003 installed, since it uses a new paradigm for Web services.
In any case, you need to create at least three directories on your hard driver per distributed application. I usually create these underneath the C:\INETPUB\WWWROOT directory, but your directories may vary. The reason I keep mine there is to keep security and backups consistent.
For this example, I created these directories:
C:\INETPUB\WWWROOT\PUBSDEMO C:\INETPUB\WWWROOT\PUBSDEMO\SCHEMA C:\INETPUB\WWWROOT\PUBSDEMO\TEMPLATE
The first directory name is up to you, but the other two need to follow underneath it. It's best if you keep the names I've got here. The first directory is where you hook up the virtual directory for SQL Server to publish the XML data, and the SCHEMA directory is used for XML schemas that shape the data. The TEMPLATE directory is used to create XML templates that you can use to hide the complexity of the XML query or even hide it from the selection syntax I'll show in you a moment. For now, I'll keep it simple and leave these two blank.
With the IIS Server installed and tested, and the directories created, locate the Start-Button menu item called Microsoft SQL Server then Configure XML Support in IIS. This brings up a panel that allows you to set the virtual directories and map them to the ones you've just created. Once inside, right-click the Default Web Site and select New then Virtual Directory.
On the General tab, set the name of the virtual directory you want. I type pubsdemo here, so my URL would look like this:
Next, map that virtual directory to the physical directory, which in my case is:
On the Security tab, select the type of security you want. I set mine to Windows Integrated, but you can also use SQL Server security. Doing so makes every connection use the same account, and by extension has the same account privileges. You'll allow the users to type queries in at the URL momentarily, so be careful with this choice. Don't allow that account to do DDL statements unless you know that's what you want.
After you set the desired security, move to the Data Source tab. Set the name of the SQL Server and the database the connection will use. In my case, that's pubs.
On the Settings tab, you set how the queries will be posted. If you are in a secure environment, you can set the top option that allows direct queries right in the URL, which is what I've done. Understand that the query and the results are clear text, so make sure you're OK with that. The other options set whether you allow POST operations to enter data back to the server from the browser, and whether you allow template queries. I set them all for this demo. Next week, I'll lock this down a bit.
On the Virtual Names tab select <New Virtual Name>, and then enter schema and select the schema type. Set the directory for the schemas. My schema directory is:
Now click Save and then repeat the process, this time entering template as the name, template as the type, and enter your template directory. Mine is:
You're all set. To test the server, enter the following format in your browser:
Replace NSALT with the name of your server, and enter any query you like after the ?= part. Make sure you include the &root=SomeRootTagName part if you use XML, otherwise the server won't have a place to "hang" the results on.
Next time, I'll show you how extend this process with templates, which makes the whole process more secure.
I won't cover XML schemas in my tutorials. You can find a great one at http://www.w3schools.com/schema/schema_intro.asp.
InformIT Tutorials and Sample Chapters
In addition to the link above, Nicholas Chase has an explanation of XML schemas here.