Table of Contents

• Implementation/Design Differences
• IIS XML MS SQL Server Virtual Directories
• Extracting Data via HTTP URLs with Internet Explorer 5 and Later
• Extracting Data via Template Queries with Internet Explorer 5 and Later

• You can configure Microsoft SQL Server 2000 (and later) to deliver XML-formatted data via Internet Information Services (IIS) 5 (and later). Typically, devlopers write applications and components that extract data from MS SQL Server databases with code in the following fashion:

• If your only purpose of creating an ASP.NET Web Form is to render data in an MS SQL Server database in the form of HTML to a client, then you would be better off spending less time devloping code by using the following design pattern instead:

• You gain access to MS SQL Server (2000 +) XML data via IIS (5 +) Virtual Directories. The following steps will show you how to setup a Virtual Directory via the Microsoft Management Console (MMC) snap-in IIS Virtual Directory Management for SQL Server:

1. Open the IIS Virtual Directory Management for SQL Server MMC span-in.
   
   
   
2. Right-click on the Default Web Site (or the Web Site name) for the IIS computer, then click on New\Virtual Directory.
   
   
   
3. In the General tab, set the Virtual Directory Name and Local Path to the Virtual Directory's IIS root folder (this can remain the default wwwroot folder).
   
   
   
4. Set security options in the Security tab.
   
   Note: if you setup security so that the local IIS IUSR_COMPUTERNAME account is used for MS SQL Server acccess, make sure that the COMPUTERNAME\IUSR_COMPUTERNAME account is setup for access to the specified database in MS SQL Server first. This type of security configuration will be necessary if you want an ASP.NET Web Form to have access to the Virtual Directory (as the ASP.NET Web Form will typically run under the context of the local IUSR_COMPUTERNAME account.
   
   
   
5. In the Data Source tab, set the name of the MS SQL Server containing the data you want to extract. Then, set the Database field to the name of the database containing the data you want to extract.
   
   
   
6. Set appropriate settings in the Settings tab.
   
   
   
7. Click on the OK button to commit your changes.
   
   
8. You should be able to now view the Virtual Directory in the MMC console.
   
   
   

Where:
• Anything in brackets is optional.
• IISComputerName: is the name of the server where the Virtual Directory was setup.
• VirtualDirectoryName: is the name of the Virtual Directory setup on the IIS server.
• SelectQuery: is any T-SQL formatted SELECT statement with the appended FOR XML AUTO clause.
  For example, SELECT * FROM Employees FOR XML AUTO
• XMLRootNode: is the parent/root node of the XML document extracted.
• XSLTFileName: the URL path to an XSLT transformation file to be applied to the extract XML data.

1. Open the IIS Virtual Directory Management for SQL Server MMC span-in.
2. Click on the Default Web Site (or the Web Site name) for the IIS computer, then click on New\Virtual Directory.
3. Double-click on the Virtual Directory you want to setup a Virtual Template Directory for.
4. Click on the Virtual Names tab.
5. Create a Virtual Name of Type template and set the Path to that of where the template file(s) is(are) to be located.
   
6. Click on Save.
7. Click on OK to commit your changes.

Where:
• Anything in brackets is optional.
• localhost: is the name of the server where the Virtual Directory was setup.
• MSSQLNorthwind: is the name of the Virtual Directory setup on the IIS server.
• template: is the name of the Virtual Template Directory setup via the Virtual Names tab on the IIS server.