XML and SQL 2000 (Part 4)

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