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:

Character

Special meaning
in the URL string

Hex value

+

Space

%20

/

Separator in URL
path

%2F

?

Start of parameters

%3F

&

Separator of
parameters

%26

%

Start of an encoded
character

%25

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles