How to use HTTP to access SQL Server

Sql_server_using_url> using HTTP to access SQL server

You can use HTTP to access Microsoft®sql server™2000. Before you can specify queries using HTTP, you must first create a virtual root with the IIS virtual Directory Management utility for SQL Server.

The HTTP access capabilities of SQL Server allow you to:

Specify the SQL query directly in the URL, for example: target=_blank>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: Target=_blank>http://iisserver/nwind?template=select+*+from+customers+for+xml+auto

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

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 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"]

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 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 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.

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

Target=_blank>http://iisserver/virtualroot/templatevirutalname/mytemplate.xml ">http://IISServer/ Virtualroot/templatevirutalname/mytemplate.xml

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

　　

SELECT *

From Customers

For XML AUTO

　　

　　

The markup 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 be 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 label

, and there is no title in the XML fragment.

Sql_server_utility> using the IIS virtual Directory Management utility for SQL Server

Before you use HTTP to access the 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.

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 a 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 the template type of virtual name that identifies the specified file (template.xml) as the template file.

Sql_server_utility>