Mapping an XML Schema to a Relational Schema Using Annotated Schemas

An application can create XML views of relational data using annotated XDR (XML-Data Reduced) schemas, which can then be queried using XPath queries. This process is conceptually similar to creating views using Transact-SQL CREATE VIEW statements, and then specifying SQL queries against the view. Annotated schemas, which are Microsoft-developed extensions to the XML Data specification, allow client applications to view a relational database as an XML document instead of a group of tables. An XML file that maps XML elements and attributes to tables and columns of a relational database is called a Mapping Schema. Applications can use these two technologies to query Microsoft® SQL Server™ without using SQL commands and without knowing the relational design of the database. For more information about XPath queries, see Using XPath Queries. For more information about XML views and annotated schemas, see Creating XML Views Using Annotated XDR Schemas.

The following example demonstrates how to build an XPath query that is functionally equivalent to this Transact-SQL statement:

SELECT o.OrderID, o.OrderDate from Orders o, Customers c, 
WHERE o.CustomerID = c.CustomerID and c.CompanyName = ?

This example passes the CompanyName, Tortuga Restaurante, as an input parameter.

The Customers and Orders tables from the Northwind database are used to create a mapping schema. This is the structure of the Customers and Orders tables, including primary and foreign key relationships.

CREATE TABLE [Customers] 
   [CustomerID] [nchar] (5) NOT NULL ,
   [CompanyName] [nvarchar] (40) NOT NULL ,
   [ContactName] [nvarchar] (30) NULL ,
   [ContactTitle] [nvarchar] (30)  NULL ,
   [Address] [nvarchar] (60) NULL ,
   [City] [nvarchar] (15) NULL ,
   [Region] [nvarchar] (15) NULL ,
   [PostalCode] [nvarchar] (10) NULL ,
   [Country] [nvarchar] (15) NULL ,
   [Phone] [nvarchar] (24) NULL ,
   [Fax] [nvarchar] (24) NULL 

   PRIMARY KEY [CustomerID]

CREATE TABLE [Orders] (
   [OrderID] [int] IDENTITY (1, 1) NOT NULL,
   [CustomerID] [nchar] (5) NULL,
   [EmployeeID] [int] NULL,
   [OrderDate] [datetime] NULL,
   [RequiredDate] [datetime] NULL,
   [ShippedDate] [datetime] NULL,
   [ShipVia] [int] NULL,
   [Freight] [money] NULL,
   [ShipName] [nvarchar] (40) NULL,
   [ShipAddress] [nvarchar] (60) NULL,
   [ShipCity] [nvarchar] (15) NULL,
   [ShipRegion] [nvarchar] (15) NULL,
   [ShipPostalCode] [nvarchar] (10) NULL,
   [ShipCountry] [nvarchar] (15) NULL

   PRIMARY KEY [OrderID]
   FOREIGN KEY [Customers].[CustomerID]

The example SQL query requires the OrderID, OrderDate, and CustomerID columns from the Orders table, and the CompanyName and CustomerID columns from the Customers table.

The application also requires a mapping schema, which in this example, is stored in the Orders.xml file in the virtual root directory.

The document contains Namespace declarations, specifically the XML-Data namespace. These table-mapping elements are also included:

• The sql:relation attribute, which is used to identify the table or view in the database. Inside each element are attributes that map to columns in the table identified by the element.
  
  
• The sql:field attribute, which is used to identify the field in the SQL table.
  
  
• The sql:relationship attribute, which is used to identify the primary and foreign key relationships between the two tables.

<?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="Order" sql:relation="Orders" >
    <AttributeType name="CustomerID" />
    <AttributeType name="OrderID" />
    <AttributeType name="OrderDate" />
    <attribute type="CustomerID" sql:field="CustomerID" />
    <attribute type="OrderID" sql:field="OrderID" />
    <attribute type="OrderDate" sql:field="OrderDate" />
</ElementType>

<ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="CompanyName" />
    <attribute type="CustomerID" sql:field="CustomerID" />
    <attribute type="CompanyName" sql:field="CompanyName" />
      <element type="Order" >
      <sql:relationship key-relation="Customers" key="CustomerID"
         foreign-key="CustomerID" foreign-relation="Orders" />
    </element>
</ElementType>
</Schema>

Using an Active Server Page (ASP), an application user generates a URL containing a company name for which he or she wants to see orders. In this example, the URL takes the form:

http://WebServer/Vroot/Orders.asp?CompanyName="Tortuga%20Restaurante"

Using the customer name passed in by the user, the ASP constructs this XPath query to run against the mapping schema:

Customer[@CompanyName="Tortuga Restaurante"]

This query string is passed to the ADO Command object and executed, returning the results in an XML stream.

The ASP begins by using the ASP Request object to capture the CompanyName passed in using the URL and storing it in a string variable called sCompanyName.

dim sCompanyName
sCompanyName = Request.QueryString("CompanyName") 

The application then creates ADO Connection and Command objects. Because the application issues commands written as XPATH queries, it must use the XPATH command dialect.

adoCmd.CommandText = "Customer[@CompanyName=" & sCompanyName & "]"
adoCmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"

The application then sets properties specific to the Microsoft OLE DB Provider for SQL Server: Mapping Schema and Base Path. The application sets the Mapping Schema property to the name of the mapping schema file, and Base Path property to the directory containing the mapping schema file.

   adoCmd.Properties("Mapping Schema") = "Orders.xml"
   adoCmd.Properties("Base Path") = "C:\INETPUB\WWWROOT\Kowalski\"
   

After the Output Stream property is set to the ASP Response object, the command can be executed. The application sets the adExecuteStream parameter of the Command object, and encloses the setting in XML tags to create an XML data island.

Response.write "<XML ID='MyDataIsle'>"
   adoCmd.Execute , , adExecuteStream
   Response.write "</XML>"
%>

At this point in the code execution, the application has passed the XML stream to the client browser. The XML stream is displayed using client-side VBScript to bind the XML document to an instance of the DOM, and by looping through each child node to build a list of OrderIDs and OrderDates using HTML.

Examples

This is the complete code listing from the ASP described previously.

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Developer Studio"/>
<META HTTP-EQUIV="Content-Type" content="text/html" charset="iso-8859-1"/>
<TITLE>XPATH Query Annotated Schema Orders.asp</TITLE>

<STYLE>
   BODY
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }
   H3
   {
      FONT-FAMILY: Tahoma;
      FONT-SIZE: 8pt;
      OVERFLOW: auto
   }

</STYLE>

<!-- #include file="adovbs.inc" -->
<%
   dim sCompanyName
   sCompanyName = Request.QueryString("CompanyName")
   If Len(sCompanyName) = 0 then
      Response.redirect "http://MYSERVER1/Kowalski/OrdersErr.asp"
   Else
Dim sConn
sConn = "Provider=SQLOLEDB; Data Source=MYSERVER1; Initial Catalog=Northwind; 
User ID=SA;Password=;"

Dim adoConn
Set adoConn = Server.CreateObject("ADODB.Connection")
   adoConn.ConnectionString = sConn
adoConn.CursorLocation = adUseClient
adoConn.Open

      Dim adoCmd
          Set adoCmd = CreateObject("ADODB.Command")
          Set adoCmd.ActiveConnection = adoConn
          adoCmd.CommandText = "/Customer[@CompanyName=" & sCompanyName & "]"
adoCmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"

       adoCmd.Properties("Mapping Schema") = "Orders.xml"
          adoCmd.Properties("Base Path") = "C:\INETPUB\WWWROOT\Kowalski\"
          adoCmd.Properties("Output Stream") = Response

Response.write "<XML ID='MyDataIsle'>"
      adoCmd.Execute , , adExecuteStream
      Response.write "</XML>"
End If
%>

<SCRIPT language="VBScript" For="window" Event="onload">
   
   Dim xmlDoc
   Set xmlDoc = MyDataIsle.XMLDocument
   xmlDoc.resolveExternals=false
   xmlDoc.async=false
      
   Dim root, child, header, OutputHeader
   Set root = xmlDoc.documentElement

   OutputHeader = document.all("header").innerHTML 
   OutputHeader = OutputHeader & "CustomerID: " & root.getAttribute("CustomerID")
   document.all("header").innerHTML = OutputHeader 
   
   For each child in root.childNodes
      dim OutputOrders, OrderList
      OutputOrders = document.all("Orders").innerHTML 
      OrderList = "<LI> Order # " & child.getAttribute("OrderID") & ", Date: " & 
child.getAttribute("OrderDate") & "</LI>"
      TotalPage = OutputOrders & OrderList
      document.all("Orders").innerHTML = TotalPage
   Next

</SCRIPT>

</HEAD>
<BODY>
   <H3>Client-side processing of XML Document MyDataIsle</H3>
   <DIV id=Header></DIV>
   <UL id=Orders></UL>
</BODY>
</HTML>

See Also