Accessing SQL Server Using HTTP

You can access Microsoft® SQL Server™ 2000 using HTTP. For more information about the URL syntax that is support by the SQL ISAPI extension, see URL Access. Before queries can be specified using HTTP, a virtual root must be created using the IIS Virtual Directory Management for SQL Server utility. For more information, see Creating the nwind Virtual Directory.

The HTTP access to SQL Server allows you to:

• Specify SQL queries directly in the URL, for example:

  http://IISServer/nwind?sql=SELECT+*+FROM+Customers+FOR+XML+AUTO&root=root
  

  The FOR XML clause returns the result as an XML document instead of a standard rowset. The root parameter identifies the single top-level element.

• Specify templates directly in the URL.

  Templates are valid XML documents containing one or more SQL statements. The templates allow you to put together data to form a valid XML document, which is not necessarily the case when queries are specified directly in the URL. For example:

  http://IISServer/nwind?template=<ROOT+xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>SELECT+*+FROM+Customers+FOR+XML+AUTO</sql:query></ROOT>
  

• Specify template files in the URL.

  Writing long SQL queries at the URL can be cumbersome. In addition, browsers may have limitations on the amount of text that can be entered in the URL. To avoid these problems, templates can be written and stored in a file. A template is a valid XML document containing one or more SQL statements and XPath queries. You can specify a template file directly in a URL, for example:

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

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

  Template files also enhance security by removing the details of database queries from the user. By storing the template file in the virtual root directory (or its subdirectories) where the database is registered, security can be enforced by removing the URL query-processing service on the virtual root, and leaving only the SQL Server XML ISAPI to process the files and return the result set.

• Write XPath queries against the annotated XML-Data Reduced (XDR) schemas (also referred to as mapping schemas).

  Writing XPath queries against the mapping schemas is conceptually similar to creating views using the CREATE VIEW statement and writing SQL queries against them, for example:

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

  In the URL:

  • SchemaVirtualName is the virtual name of schema type that is created using the IIS Virtual Directory Management for SQL Server utility.
    
    
  • Customer[@CustomerID="ALFKI"] is the XPath query executed against the schemafile.xml specified in the URL.
• Specify database objects directly in the URL.

  The database objects, such as tables and views, can be specified as part of the URL, and an XPath can be specified against the database object, for example:

  http://IISServer/nwind/dbobjectVirtualName/XpathQuery
  

  In the URL, dbobjectVirtualName is the virtual name of dbobject type that is created using IIS Virtual Directory Management for SQL Server utility.

  Note  When an operation that requires resources such as memory (creating temporary tables and temporary stored procedures, declaring cursors, executing sp_xml_preparedocument, and so on) is executed at the URL, the resources must be freed by executing appropriate corresponding commands (such as, DROP TABLE, DROP PROCEDURE, DEALLOCATE the cursor, or EXECUTE sp_xml_removedocument).

XML Documents and Document Fragments

When you execute a template or a query with the root parameter, the result is a full XML document with a single top-level element. For example, this URL executes a template:

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

This 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 the top-level single element for the resulting XML document.

The queries can be specified directly in the URL. In this case, the root parameter specifies the top-level element of the document returned:

http://IISServer/VirtualRoot?sql=SELECT * FROM Customers FOR XML AUTO?root=root 

If you write the same query without the root parameter, an XML document fragment (an XML document without the single top-level element) is returned. This fragment has no header information. For example, this URL returns a document fragment:

http://IISServer/VirtualRoot?sql=SELECT * FROM Customers FOR XML AUTO

The byte-order mark identifying the document encoding is returned when you request an XML document. A byte-order mark is a standard sequence of bytes identifying encoding type of the XML document. The XML parsers use this byte-order mark to determine the document encoding (such as Unicode). For example the byte-order mark, oxff, 0xfe identifies the document as Unicode. By default, the parser assumes the UTF-8 as the document encoding.

The byte-order mark is not returned when you request a XML fragment, because the byte-order mark belongs to the XML document header, which is missing in the XML fragment.

See Also

Retrieving XML Documents Using FOR XML

Using IIS Virtual Directory Management for SQL Server Utility