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,
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:
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:
Make sure to replace the
<?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
<?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:
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:
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.