Use the IIS virtual Directory Management utility for SQL Server

iis|server| virtual Directory

Using HTTP to access Microsoft? SQL Server? 2000 database, you must install the appropriate virtual directory. On a computer running Microsoft Internet Information Services (IIS), define and note using the IIS virtual Directory Management utility for SQL Server (click Configure SQL XML support in IIS in the SQL Server Tools program group) A new virtual directory, known as the virtual root. This utility instructs IIS to create an association between a new virtual directory and a Microsoft SQL Server instance. For information about the utility user interface, see the IIS Virtual Directory Management utility.

The name of the IIS server and virtual directory must be specified as part of the URL. The information in the virtual directory, including logins, passwords, and access rights, is used to establish a connection to a specific database and execute the query.

You can specify a URL:

Direct access to database objects, such as tables.
In this case, the URL will include the virtual name of the DBObject type.

Executes the template file.
A template is a valid XML document that consists of one or more SQL statements. When you specify a template file in a URL, the SQL commands stored in the template file are executed. You can specify SQL queries directly in the URL, but this is not recommended, given security.

Executes an XPath query.
Performs an XPath query on a annotated mapped schema file that specifies as part of the URL.

Virtual name to make a template file, a mapped schema file, or a database object (such as a table or view) part of a URL, you must create a virtual name for the template, schema, and dbobject types. Specifies the virtual name as part of the URL to execute the template file, perform an XPath query on the mapped schema file, or access the database directly.

The virtual name type (template, schema, dbobject) specified in the URL is also used to determine the type of file (template file or mapping schema file) that is specified in the URL. For example

The following URL uses a template to access the SQL Server database:

Http://IISServer/nwind/TemplateVirtualName/Template.xml

Templatevirtualname is the template type of virtual name that identifies the specified file (template.xml) as the template file.

Create a nwind virtual directory

New information-SQL Server SP3.

The following example creates a nwind virtual directory. In most of the instructions for Microsoft? SQL Server? 2000 of the URL access examples, all use the nwind virtual directory.

Before you create a nwind virtual directory, you need a physical directory associated with the virtual directory that you will create (for example, C:\Inetpub\Wwwroot\nwind, where nwind is the physical directory associated with the NWIND virtual directory that will be created in the following procedure).

You also need to create two subdirectories under the physical directory associated with the virtual directory (for example, C:\Inetpub\Wwwroot\nwind\template and

C:\Inetpub\Wwwroot\nwind\schema). These are the directories associated with the virtual names of the template and schema types, both of which are created as part of the creation of the nwind virtual directory.

Create a nwind virtual directory

In the Microsoft SQL Server program group, click Configure SQL XML support in IIS.

Expand the server, and then click the Web site you want.

On the Action menu, point to the New submenu, and then click Virtual Directory. The property page for the new virtual directory appears on the screen.

On the General tab of the new Virtual Directory Properties dialog box, enter the name of the virtual directory. For this example, type the nwind and physical directory path (for example, C:\Inetpub\Wwwroot\nwind, assuming the nwind subdirectory has been created in the C:\Inetpub\Wwwroot directory). Alternatively, you can use the Browse button to select the directory.

On the Security tab, select SQL Server and use Windows integrated authentication.

On the Data Sources tab, enter the name of the server in the SQL Server box, such as (local), or, if more than one instance is installed on the specified computer, enter the name of the SQL Server2000 instance (optional). In the Database box, enter the name of Northwind as the default database.

On the Settings tab, select the Allow URL query, allow template query, allow XPath, and allow POST options.

On the Virtual Name tab, click the New button to create a virtual name for the template type.
In the Virtual Name Configuration dialog box:

In the Virtual Name box, enter

Template (can be any name specified by the user). In the Type list, select Templates. The input path (for example, C:\Inetpub\Wwwroot\nwind\template, assumes a subdirectory template under the physical directory associated with the virtual directory, but does not check that the directory exists). Click the Save button to save the virtual name.

On the Virtual Name tab, click the New button to create a virtual name for the schema type.
Enter a schema in the Virtual name box (can be any user-specified name). In the Type list, select Schema. The input path (for example, C:\Inetpub\Wwwroot\nwind\schema, assumes a subdirectory schema under the physical directory associated with the virtual directory). Click the Save button to save the virtual name.

On the Virtual Name tab, click the New button to create a virtual name for the template and schema type.
Enter DBObject in the Virtual Name box (which can be any user-specified name). In the Type list, select DBObject. Click the Save button to save the virtual name.

Click the OK button to save the settings.

This creates the virtual directory nwind. By default, the specified query is executed against the Northwind database using this virtual directory.

To test the virtual directory, type in the browser:/nwind?sql=select ">http://<iisserver>/nwind?sql=select * from Employees for XML auto& Root=root and press the ENTER key.

accessing SQL Server using HTTP

New information-SQL Server SP3.

Can I use HTTP to access Microsoft? SQL Server? 2000. For more information about the URL syntax supported by the SQL ISAPI extension, see URL Access. Before you can specify queries using HTTP, you must first create a virtual root with the IIS virtual Directory Management utility for SQL Server. For more information, see Creating nwind virtual directories.

The HTTP access capabilities of SQL Server allow you to:

Specify the SQL query directly in the URL, for example:
Http://iisserver/nwind?sql=select+*+from+customers+for+xml+auto&root=root

Specifies that the FOR XML clause returns the result in the form of an XML document rather than a standard rowset. The root parameter identifies a single top-level element.

Specify the template directly in the URL.
A template is a valid XML document that contains one or more SQL statements. Templates allow you to put data together to form a valid XML document, but not necessarily when you specify a query directly in the URL. For example:

Select+*+from+customers+for+xm ' >http://iisserver/nwind?template=<root+xmlns:sql= ' urn: Schemas-microsoft-com:xml-sql "><sql:query>select+*+from+customers+for+xm

L+auto</sql:query></root>

Specify the template file in the URL.
Writing a long SQL query in a URL can be cumbersome. In addition, the browser may have restrictions on the amount of text that can be entered in the URL. To avoid these problems, you can write a template and store it in a file. A template is a valid XML document that contains one or more SQL statements and XPath queries. Template files can be specified directly in the URL, for example:

Http://IISServer/nwind/TemplateVirtualName/templatefile.xml

In the URL, Templatevirtualname is the virtual name of the template type created using the IIS virtual Directory Management utility for SQL Server.

The template file also removes the details from the user's database query to enhance security. By storing the template file in the virtual root (or its subdirectories) where the database is registered, removing the URL query processing service on the virtual root and allowing only SQL Server XML ISAPI to process files and return result sets enhances security.

Specifies an XPath query that executes on an annotated XML data simplified (XDR) schema (also known as a mapping schema).
Conceptually, writing an XPath query to a mapping schema is similar to creating a view using the CREATE VIEW statement and writing a SQL query to the map, for example:

http://iisserver/nwind/schemavirtualname/schemafile.xml/customer[@CustomerID = "ALFKI"]

In this URL:

Schemavirtualname is the virtual name of the schema type created using the IIS virtual Directory Management utility for SQL Server.

customer[@CustomerID = "ALFKI"] is an Xpath query executed on the schemafile.xml specified in the URL.
Specify the database object directly in the URL.
You can designate database objects, such as tables and views, as part of a URL, and specify an Xpath query for database objects, such as:

Http://IISServer/nwind/dbobjectVirtualName/XpathQuery

In this URL, Dbobjectvirtualname is the virtual name of the DBObject type created using the IIS virtual Directory Management utility for SQL Server.

Explains that when you perform operations that require resources (such as memory) in a URL (creating temporary tables and temporary stored procedures, declaring cursors, executing sp_xml_preparedocument, and so on), you must perform appropriate commands (such as drop table, drop PROCEDURE, DEALLOCATE cursors or EXECUTE sp_xml_removedocument) to free resources.

XML documents and document fragments when you execute a template or query with the root parameter, the result is a complete XML document with a single top-level element. For example, the following URL executes a template:

Http://IISServer/VirtualRoot/TemplateVirutalName/MyTemplate.xml

The following is a sample template file (MyTemplate.xml):

<root xmlns:sql= "Urn:schemas-microsoft-com:xml-sql" >
<sql:query>
SELECT *
From Customers
For XML AUTO
</sql:query>
</ROOT>

The <ROOT> tag in the template provides a single top-level element for the resulting XML document.

You can specify the query directly in the URL. In this example, the root parameter specifies that the top-level element in the document is returned:

Http://IISServer/VirtualRoot?sql=SELECT * from Customers for XML Auto&root=root

If you do not use the root argument when writing the above query, an XML document fragment (that is, an XML document that lacks a single top-level element) is returned. The fragment has no header information. For example, the following URL will return a document fragment:

Http://IISServer/VirtualRoot?sql=SELECT * from Customers for XML AUTO

When an XML document is requested, a byte order mark is returned that identifies the document encoding type. A byte order mark is a standard byte order that identifies the encoding type of an XML document. The XML parser uses this byte order token to determine the document encoding type (such as Unicode). For example, the byte order mark "Oxff, 0xFE" identifies the document as Unicode. By default, the parser assumes UTF-8 is the document encoding type.

Byte order marks are not returned when an XML fragment is requested because the byte order token belongs to the XML document title, and the XML fragment does not have a caption.

Security Note the Microsoft SQLXML WEB release enhances support for this XML feature. To use the latest enhancements, install Microsoft from a Microsoft Web site

SQLXML 3.0 Service Pack 1. The documentation that is included with this Service Pack provides information about the security issues that should be considered when developing the application.

Turn from: Dynamic Network production guide www.knowsky.com