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:

• "Allow sql=... or template=... URL queries"
• "Allow posted updategrams"
• "Allow template queries"
• "Allow XPath"
• "Allow POST"

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.

• Type "Northwind" in the Virtual Directory Name box.
• Switch to the Security tab and specify the options that will provide access to SQL server in your environment.
• Use Data Source tab to provide the name of SQL server and either type in or select from the drop-down list Northwind database.
• Finally, on the Settings tab, select the "Allow sql=... or template=... URL queries" option and click on OK button. This will result in the creation of a Virtual Directory, which will appear in the details pane of the MMC window.

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):

http://webserver/virtualdirectory?{sql=SQLString|template=XMLTemplate}
                                    [&Parameter=Value[&Parameter=Value]]

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:

• Root - is needed because the T-SQL SELECT statement with FOR XML clause does not produce a well formed XML document, but instead an XML fragment that is missing the root element. Value assigned to the Root parameter becomes the root element of the resulting XML document. The value is arbitrary, as long it conforms to the XML naming standards.

• ContentType - determines the type of document returned to the browser. Typically, it would be set to text/xml, which would allow proper rendering of the results to the XML aware browser. However, there might be cases where you want to be able to process the results of the query according to your own rules or the data returned is not formatted according to XML rules (e.g. it is a binary image file). ContentType parameter to text/html.

• OutputEncoding - designates the character set to be used for the output.

• XSL - is used to specify the XSL stylesheet to be applied to the output displayed in the browser window. XSL stylesheet applies HTML formatting to individual elements and attributes of an XML document.

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):

http://webserver/northwind?sql=SELECT+*+FROM+Shippers+FOR+XML+AUTO&root=ShippersTable

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

http://webserver/northwind

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):

http://webserver/northwind?sql=SELECT+*+FROM+Shippers+WHERE+
CompanyName+LIKE+%20'S%25'+FOR+XML+AUTO+&root=ShippersStartingWithS

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:

• Root - top level element (equivalent of the Root parameter in the "sql=" URL string notation). The word Root needs to be replaced with the name of the root element.
• <sql:header> - designates section containing parameters that are passed to the SQL statements within <sql:query> section
• <sql:parameter> - contains parameters passed to the SQL statements within <sql:query> section, in the format <sql:param name='ParameterName'>Value</sql:param>, where ParameterName and Value would be replaced by the name and value of the parameter.
• <sql:query> - designates section containing one or more T-SQL statements
• sql:xsl - determines the XSL stylesheet to be applied to the resulting output

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.

» See All Articles by Columnist Marcin Policht