XML and SQL 2000 (Part 6)

In the previous
article
, I explained the meaning of the "Allow posted updategrams"
and "Allow template queries" options on the Settings tab of the Virtual
Directory Properties dialog box in IIS Virtual Directory Management for SQL
Server tool. I also presented examples of taking advantage of these options to
return results of the T-SQL queries in the XML format via HTTP. In this
article, I will continue discussion on this subject, focusing on the next
option on the Settings tab – "Allow XPath".

For a brief introduction to XPath, you might want to refer to the
second article of this series
. As you might recall, XPath provides a way to
traverse a tree representing an XML document to reach any of its elements and
attributes. XPath uses its own syntax, which allows uniquely identifying a
specific position in an XML tree. Due to its characteristics, XPath is used to
point to one or more specific elements and attributes of an XML document.

As before, our goal is to query relational data in a SQL database and return
the results in the XML format via HTTP. This time, however, our approach will
be different in two aspects. Firstly, our query will be formulated as XPath
statement – we will use XPath to specify which elements of the XML document we
are interested in. Secondly, since we query the XML document, not database
data, we need a way to map SQL tables and columns into XML elements and
attributes. This will be done using an XML mapping schema, which is a special
type of XML schema (so in essence, we will use XPath to query XML mapping
schema).

The main purpose of an XML schema is to provide a description of an XML
document. It contains definitions of its elements and attributes. It can define
properties of each such as name, data type, default value, minimum or maximum
number of instances of each, and relative relationship between elements, subelements,
and attributes. The original version of SQLXML (included in the SQL Server
2000) supports only XML Data Reduced (XDR) schema (original de-facto standard),
while starting with SQLXML version 2.0, you can also use XML Schema Definition
(XSD) schemas (officially ratified in 2001 standard). In this article, I will
be using XDR schema. The syntax of the XDR schema is fairly self-explanatory
but its content is strictly dependent on requirements placed on the content of
its corresponding XML document and therefore needs to be determined on a
case-by-case basis. For documentation on the XDR syntax, you can refer to XDR Schema
Developer’s Guide
.

In the most straightforward cases, an XML document reflects exactly the data
stored in the SQL database. For example, we can easily create an XML document
representing data stored in a SQL table. We will use the Shippers table from
the Northwind database to demonstrate this. The table has a simple structure –
it consists of three columns: ShipperID (identity), Shippers and Phone (both of
type nvarchar). The XDR schema describing such structure would look similar to
this one:

<?xml version="1.0"?>
<Schema 
	xmlns_dt="urn:schemas-microsoft-com:datatypes">

	<ElementType name="Shippers">
		<element type="ShipperID"/>
		<element type="CompanyName"/>
		<element type="Phone"/>
	</ElementType>

	<ElementType name="ShipperID" content="textOnly"/>
	<ElementType name="CompanyName" content="textOnly"/>
	<ElementType name="Phone" content="textOnly"/>

</Schema>

Notice that the schema starts (like any other well-formed XML document) with
the schema declaration. Next, we have references to two different namespaces –
the first one, "urn:schemas-microsoft-com:xml-data",
defines the document as a Microsoft XDR schema document, the second one urn:schemas-microsoft-com:datatypes",
allows us to specify the datatype of an element or attribute (it is not used in
this example, but will appear in the next one). The third schema "urn:schemas-microsoft-com:xml-sql",
provides the ability to map between SQL tables and rows on one side and XML
elements and attributes on the other, and will serve to create custom mapping
schemas, but it is not needed here, since we are using default mapping. One
caveat that you need to keep in mind when dealing with default mapping XDR
schemas is that each column in the SQL database table needs to be represented
as a separate element with the content set to "textOnly" (as we did
in our example).

Next, you will need to create a reference to the schema using IIS Virtual
Directory Management for SQL server. This is done by taking the following steps:

  • Create a subfolder under the file system folder to which your Northwind
    virtual directory points. In our example, we will call it Schema.

  • Create a text file in the Schema folder and copy the content of
    the XDR schema for Shippers XML document to it. Save it using any (preferably
    descriptive) name. We will call it Shippers.xdr.

  • Assign a virtual name of type schema to the Schema folder created
    in the first step by selecting the Virtual Names tab on the Northwind virtual
    directory properties dialog box (in the IIS Virtual Directory Management for
    SQL Server). Click on the New button, and enter a virtual name, (We will use
    XDR – but you can choose any other name), type (schema), and path pointing to
    the physical location of the Schema folder.

  • Select the Allow XPath option on the Settings tab of the Northwind
    virtual directory Properties dialog box.

  • In addition, before you start experimenting with XML schemas and
    IIS Virtual Directory Management for SQL server, you should change the default
    settings on the Advanced tab of the virtual directory properties dialog box.
    One of the checkboxes in the "Caching options" section controls
    caching of mapping schemas and is, by default, cleared (for performance
    reasons). You should disable this option for the duration of your testing; otherwise,
    your schemas will not be reloaded automatically after you modify them.
    Otherwise, you will have to restart the entire application (option in the
    Action menu) after each schema modification.

At this point, you are ready to run the XPath query against the XDR schema
with the default mapping. In the browser window, type in the following:

http://servername/northwind/xdr/Shippers.xdr/Shippers?root=Shippers

Make sure to replace the servername
entry with the name of your server hosting the IIS component. Note that our XPath
is in the simplest form – pointing to the root element, which returns the entire
XML document. Note that you also need to specify the root parameter (we call it
Shippers, but the name again can be arbitrarily chosen) to ensure that the
outcome will be a well-formed XML document. Your browser should display results
similar to the following:

<?xml version="1.0" encoding="utf-8" ?> 
<ShippersList>
  <Shippers>
    <ShipperID>1</ShipperID> 
    <CompanyName>Speedy Express</CompanyName> 
    <Phone>(503) 555-9831</Phone> 
  </Shippers>
  <Shippers>
    <ShipperID>2</ShipperID> 
    <CompanyName>United Package</CompanyName> 
    <Phone>(503) 555-3199</Phone> 
  </Shippers>
  <Shippers>
    <ShipperID>3</ShipperID> 
    <CompanyName>Federal Shipping</CompanyName> 
    <Phone>(503) 555-9931</Phone> 
  </Shippers>
</ShippersList>

If you cannot use the default mapping, you will need to resort to the
annotated custom mapping schema. Such schema includes additional information,
in the form of schema annotations, documenting relationship between database
tables and columns on one side and XML elements and attributes on the other.
The following example provides an alternative way of creating XML schema,
representing data from the same table as before, this time including an example
of using references to the "urn:schemas-microsoft-com:xml-sql"
schema and "urn:schemas-microsoft-com:datatypes"
namespace:

<?xml version="1.0" ?>
<Schema 
	xmlns_dt="urn:schemas-microsoft-com:datatypes"
	xmlns_sql="urn:schemas-microsoft-com:xml-sql">

	<ElementType name="Shipper" sql_relation="Shippers">
		<AttributeType name="ShipperID" required="yes" dt_type="int"/>
		<AttributeType name="CompanyName" required="yes" dt_type="string"/>
		<AttributeType name="Phone" required="no" dt_type="string"/>

		<attribute type="ShipperID"/>
		<attribute type="CompanyName"/>
		<attribute type="Phone"/>
	</ElementType>
</Schema>

Now, type the following URL string:

http://localhost/nwind/xdr/Shippers1.xdr/Shipper?root=Shippers

will return the following XML document:

<?xml version="1.0" encoding="utf-8" ?> 
  <Shippers>
    <Shipper ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> 
    <Shipper ShipperID="2" CompanyName="United Package" Phone="(503) 555-3199" /> 
    <Shipper ShipperID="3" CompanyName="Federal Shipping" Phone="(503) 555-9931" /> 
  </Shippers>

Obviously the XPath can be more complex; you can experiment with different
values by referring to the the
second article of this series
. For example, to return information about the
shipper with ShipperID equal to 1, you could run:

http://localhost/nwind/xdr/Shippers.xml/Shipper[@ShipperID="1"]

which would give you the following outcome.

 <Shipper ShipperID="1" CompanyName="Speedy Express" Phone="(503) 555-9831" /> 

In the next article of this series, I will explain the last option on the
Settings tab – "Allow Post" and provide examples of its use.

»


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