XML and SQL 2000 (Part 6)

July 7, 2003

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="urn:schemas-microsoft-com:xml-data"
	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="urn:schemas-microsoft-com:xml-data"
	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