XML and SQL 2000 (Part 4)
June 10, 2003
In the fourth article of the series dealing with XML related features of SQL Server 2000, I will discuss the implications of various configuration options available when using IIS Virtual Directory Management for SQL Server. As described previously, the Settings tab of the Virtual Directory Properties dialog box offers the following choices:
This article will provide examples explaining the meaning and implications of the first of these choices. Before we start, we need to create our sample virtual directory (linked to the Northwind database) that we will be working with.
Start by launching the IIS Virtual Directory Management for SQL server, right click on the Web site icon in the left pane and select New Virtual Directory from the context sensitive menu.
Now we are ready to explore the first method of interfacing with SQL Server via HTTP protocol based on "Allow sql=... or template=... URL queries" option. In general, the format of URL string complies with the following syntax (even though the URL string appears on two separate lines, it would need to be entered as a single line in the browser URL address box):
As you can see, the first part of the URL query, consists of the standard "http://" designating the protocol to be used, and the path to the virtual directory on the web server. The second part can be either an SQL string specifying the T-SQL statement to be executed against the target directory or a string representing an XML template. Finally, the third part includes parameters, which affect the way results are returned to the browser. The possible values are:
We will look into URL query in the "sql=SQLString" format first, since it is the simplest one. Launch the browser on a client computer. In the Address box, type in the following (remember to replace the webserver entry below with the name or IP address of the web server):
As long as your browser has the built-in XML Parser (Internet Explorer 5.0 and Netscape Navigator 6.0 or later), you will be presented with the content of the Shippers table displayed as XML document.
<?xml version="1.0" encoding="utf-8" ?> - <ShippersTable> <Shippers ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> <Shippers ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" /> <Shippers ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" /> </ShippersTable>
Let's review the format of the URL string. Its first part consists of
This is the standard way of accessing a virtual directory on a web server, familiar even to casual web surfers. The second part is preceded by a single question mark, and it consists, in this case, of two subparts. The first subpart is the actual T-SQL statement, with the FOR XML clause (for review of its syntax, refer to the first article of this series. This, however, can be any T-SQL statement or a stored procedure. The second subpart consists of a single parameter, separated from the T-SQL statement by an ampersand sign (&). As explained before, this parameter, named root, is needed because the output of a T-SQL SELECT statement with FOR XML clause does not contain the root element. You can fix this problem by including the "root=ShippersTable" parameter in the URL string, which will set the root element of the resulting XML document to "ShippersTable".
In addition, as you have probably noticed, spaces in the T-SQL statement are replaced with the "+" sign, since a URL string cannot contain spaces. Note that we also could have left spaces there (as in the original T-SQL statement) because Internet Explorer would automatically convert them into their hexadecimal equivalent (%20). I tend to use "+" signs for readability, but this is just a personal preference - either format works fine. However, you need to remember that there are a number of characters that have a special meaning when used as part of URL path. If these characters appear in the T-SQL statement, you will need to convert them according to the following table:
For example, let's assume that you want to find the list of all shippers whose Company Name starts with "S". To accomplish this, you would run the following query:
SELECT * FROM Shippers WHERE CompanyName LIKE 'S%'
In order to execute the same query via HTTP, you would enter the following URL string in the browser (even though the text is wrapped to fit in the window, it would appear as a single line in your browser):
As you can see, I replaced the 'S%' element with 'S%25', following the rule in the table above.
Now, that we are familiar with the option of using "sql=" URL string, let's take a look at its alternative - "template=XMLTemplate" notation. The majority of options are equivalent to these available with "sql=" URL strings. An XMLTemplate string has the following format:
<Root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="XSLFileName" > <sql:header> <sql:parameter>..</sql:parameter> <sql:parameter>..</sql:parameter>...n </sql:header> <sql:query> sql statement(s) </sql:query> <sql:xpath-query mapping-schema="SchemaFileName.xml"> XPath query </sql:xpath-query> </Root>
where the individual components are:
Let's consider the following example, which will produce identical output to the one created with the "sql=" URL string. Type in the following in the Address box of the browser on your client's computer (this need to be typed in as a single line):
http://webserver/Northwind?template= <ShippersTable+xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:query>SELECT+*+FROM+Shippers+FOR+XML+AUTO </sql:query> </ShippersTable>
Once you execute the query, you will see the familiar results displayed in the browser window.
In this article, we presented examples illustrating use of "sql=" and "template=" URL queries. We will be continuing this series by presenting other ways of interfacing with SQL server via HTTP and XML, such as updategrams, template files and XPath queries.