How to access SQLServer through HTTP

SQL _server_using_url> Use HTTP to access SQL Server

You can use HTTP to access Microsoft®SQL Server™2000. Before you can use HTTP to specify a query, you must first create a virtual root with the IIS virtual directory management utility for SQL Server.

The HTTP access capability of SQL Server enables you:

Specify the SQL query directly in the URL, for example, target = _ blank> http: // IISServer/nwind? SQL = SELECT + * + FROM + Customers + FOR + XML + AUTO & root = root

Specify the for xml clause to return results in the form of an XML document rather than a standard row set. The Root parameter can identify 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. The template allows you to put data together to form a valid XML document, but this is not necessarily the case when you specify the query directly in the URL. Example: target = _ blank> http: // IISServer/nwind? Template = SELECT + * + FROM + Customers + FOR + XML + AUTO

Specify the template file in the URL.

It is very troublesome to write long SQL queries into URLs. In addition, the browser may limit 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. You can directly specify the template file in the URL, for example, http: // IISServer/nwind/TemplateVirtualName/templatefile. xml

Target = _ blank> http: // IISServer/nwind/TemplateVirtualName/templatefile. xml "> 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 deletes detailed information from the user's database query to enhance security. By storing the template file in the virtual root directory (or its subdirectory) where the database is registered, delete the URL Query Processing Service on the virtual root and only allow SQL Server XML ISAPI to process files and return result sets, thus enhancing security.

Specifies the XPath query executed on the simplified XML data (XDR) architecture (also known as the ing architecture) with annotations.

Conceptually, writing an XPath query for the ing architecture is similar to creating a VIEW using the create view statement and compiling an SQL query for the VIEW, for example, http: // IISServer/nwind/SchemaVirtualName/schemafile. xml/Customer [@ CustomerID = "ALFKI"]

Target = _ blank> http: // IISServer/nwind/SchemaVirtualName/schemafile. xml/Customer [@ CustomerID = "ALFKI"] "> http: // IISServer/nwind/SchemaVirtualName/schemafile. xml/Customer [@ CustomerID = "ALFKI"]

In this URL:

SchemaVirtualName is a schema-type virtual name created using the IIS virtual directory management utility for SQL Server.

Customer @ CustomerID = "ALFKI"] is the Xpath query executed on the schemafile. xml specified in the URL.

Specify the database object directly in the URL.

You can specify a database object as part of a URL and specify an Xpath query for the database object, for example, target = _ blank> 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.

This indicates that when you perform operations that require resources (such as memory) in the URL (create temporary tables and temporary storage procedures, declare the cursor, and execute sp_xml_preparedocument, you must EXECUTE appropriate commands (such as drop table, drop procedure, DEALLOCATE cursor, or EXECUTE sp_xml_removedocument) to release resources.

XML documents and documents

When a template or query is executed with the root parameter, the result is a complete XML document with a single top-level element. For example, the following URL execution template: http: // IISServer/VirtualRoot/TemplateVirutalName/MyTemplate. xml

Target = _ blank> http: // IISServer/VirtualRoot/TemplateVirutalName/MyTemplate. xml "> http: // IISServer/VirtualRoot/TemplateVirutalName/MyTemplate. xml

The following is an example template file (MyTemplate. xml ):

　　

SELECT *

FROM MERs

FOR XML AUTO

　　

　　

The tag in the template provides a single top-level element for the result XML document.

You can directly specify the query in the URL. In this example, the root parameter specifies the top-level element in the returned document: http: // IISServer/VirtualRoot? SQL = SELECT * FROM MERs FOR XML AUTO? Root = root

If the root parameter is not used when writing the preceding query, an XML document segment is returned (that is, an XML document without a single top-level element ). This clip has no title information. For example, the following URL will return a document segment: http: // IISServer/VirtualRoot? SQL = SELECT * FROM MERs FOR XML AUTO

When an XML document is requested, the byte sequence mark used to identify the document encoding type is returned. The byte sequence mark is the standard byte sequence used to identify the encoding type of the XML document. The XML analyzer uses this byte sequence mark to determine the document encoding type (such as Unicode ). For example, the byte sequence mark "oxff, 0xfe" identifies the document as Unicode. By default, analyzer assumes that the UTF-8 is a document encoding type.

When an XML segment is requested, the byte sequence mark is not returned because the byte sequence mark belongs to the XML document mark.

But no title in the XML fragment.

SQL _server_utility> use the IIS virtual directory management utility for SQL Server

Access Microsoft through HTTP®SQL Server™2000 the appropriate virtual directory must be installed before the database is installed. On a computer running Microsoft Internet Information Service (IIS, use the IIS virtual directory management utility for SQL Server (click "Configure SQL xml support in IIS" in the "SQL Server Tools" Program Group) to define and register a new virtual directory, the so-called virtual root. This utility instructs IIS to create an association between a new virtual directory and a Microsoft SQL Server instance.

The name of the IIS server and virtual directory must be specified as part of the URL. The information in the virtual directory (including logon, password, and access permission) is used to establish a connection with a specific database and perform queries.

You can specify the URL:

Directly access database objects, such as tables.

In this case, the URL will include a virtual name of the dbobject type.

Execute the template file.

A template is a valid XML document composed of one or more SQL statements. When a template file is specified in the URL, the SQL command stored in the template file is executed. You can specify the SQL query directly in the URL. However, we do not recommend that you do this considering the security.

Execute the XPath query.

Perform an XPath query on the annotated ing architecture file specified as part of the URL.

Virtual name

To make template files, ing schema files, or database objects (such as tables or views) part of a URL, you must create a virtual name of the template, schema, and dbobject type. Specify the virtual name as part of the URL to execute the template file, perform XPath queries on the ing architecture file, or directly access the database.

The virtual name types (template, schema, dbobject) specified in the URL are also used to determine the file types (template files or ing schema files) specified in the URL ). For example, the following URL uses a Template to access the SQL Server database: http: // IISServer/nwind/TemplateVirtualName/Template. xml

Target = _ blank> http: // IISServer/nwind/TemplateVirtualName/Template. xml "> http: // IISServer/nwind/TemplateVirtualName/Template. xml

TemplateVirtualName is a virtual name of the template type, used to identify the specified file (Template. xml) is a template file.

SQL _server_utility>