Creating XML Views Using Annotated XDR Schemas

XML and Internet Support

Creating XML Views Using Annotated XDR Schemas

You can create XML views of relational data using XDR (XML-Data Reduced) schemas. These views can then be queried using XPath queries. This is similar to creating views using CREATE VIEW statements and specifying SQL queries against the view.

An XML schema describes the structure of an XML document and also various constraints on the data in the document. When you specify XPath queries against the schema, the structure of the XML document returned is determined by the schema against which the XPath query is executed.

In Microsoft® SQL Server™ 2000, the XML-Data Reduced (XDR) language is used to create the schemas. The XDR is flexible and overcomes some of the limitations of the Document Type Definition (DTD), which also describes the document structure. Unlike DTDs, XDR schemas describe the structure of the document using the same syntax as the XML document. Additionally, in a DTD, all the data contents are character data. XDR language schemas allow you to specify the data type of an element or an attribute.

In an XDR schema, the <Schema> element encloses the entire schema. As properties of the <Schema> element, you can describe attributes that define the schema name and the namespaces in which the schema reside. In the XDR language, all element declarations must be contained within the <Schema> element.

The minimum XDR schema is:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data">
   ...
</Schema>

The <Schema> element is derived from the xml-data namespace (urn:schemas-microsoft-com:xml-data).

Note This documentation assumes that you are familiar with XML-Data language.

Annotations to the XDR Schema

You can use an XDR schema with annotations that describe the mapping to the database to query the database and return the results in the form of an XML document. SQL Server 2000 introduces a number of annotations that you can use to map the XDR schema to the database tables and columns. XPath queries can be specified against the XML view created by the XDR schema to query the database and obtain results as an XML.

This is an alternative to the more complex process of writing a SQL query that uses the FOR XML EXPLICIT mode for describing the XML document structure as part of the query For more information about SELECT queries with the FOR XML EXPLICIT mode, see Using EXPLICIT Mode. However, for overcoming most of the limitations of XPath queries against mapping schemas, use SQL queries with the FOR XML EXPLICIT mode to return results in form of an XML document.

If you have public XDR schemas (such as a Microsoft BizTalk™ schemas), you can perform either of these:

Write the FOR XML EXPLICIT mode query so the data that is generated is valid against the public XDR schema; however, writing FOR XML EXPLICIT queries can be cumbersome.
Make a private copy of the public XDR schema. Then add annotations to this private copy, thus generating a mapping schema. You can specify XPath queries against the mapping schema. As a result, what the query generates is the data in the namespace of the public schema. Creating annotated schemas and specifying XPath queries against them is a much simpler process than writing the complex FOR XML EXPLICIT queries. The illustration shows the process.

Note The Microsoft BizTalk™ Framework is an effort to define a standard XML format to common business objects, such as Contacts, Orders, and Appointments. You can find copies of these business schemas at http://biztalk.org.

Mapping Schema

In the context of the relational database, it is useful to map the arbitrary XDR schema to a relational store. One way to achieve this is to annotate the XDR schema. An XDR schema with the annotations is referred to as a mapping schema, which provides information pertaining to how XML data is to be mapped to relational store. A mapping schema is, in effect, an XML view of the relational data. These mappings can be used to retrieve relational data as an XML document.

Microsoft SQL Server 2000 introduces a number of annotations that can be used in the XDR schema to map the elements and attributes to the database tables and columns. You can specify queries against the mapping schemas (XML views) using XPath (XML Path). The mapping schema describes the resulting document structure.

Namespace for Annotations

In an XDR schema, the annotations are specified using this namespace: urn:schemas-microsoft-com:xml-sql.

The example show that the easiest way to specify the namespace is to specify it in the <Schema> tag . The annotations must be namespace-qualified to the urn:schemas-microsoft-com:xml-sql namespace.

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql"
               >
    ...........
</Schema>

The namespace prefix that is used is arbitrary. In this documentation, the sql prefix is used to denote the annotation namespace and to distinguish annotations in this namespace from those in other namespaces.

Namespace for Data Types

XDR schemas allow you to specify the data type of an element or an attribute. The data types are specified using this namespace: urn:schemas-microsoft-com:datatypes.

This is the minimum XDR schema with the namespace declarations:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql"
        xmlns:dt="urn:schemas-microsoft-com:datatypes">
   ...
</Schema>

The namespace prefix that is used is arbitrary. In this documentation, the dt prefix is used to denote the data type namespace and to distinguish annotations in this namespace from those in other namespaces.

The <Schema> element is derived from the xml-data namespace: urn:schemas-microsoft-com:xml-data.

Example of an XDR Schema

This example shows how annotations are added to the XDR schema. This XDR schema consists of an <Employee> element and the EmpID, Fname, and Lname attributes.

<?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="Employee" >
    <AttributeType name="EmpID" />
    <AttributeType name="FName" />
    <AttributeType name="LName" />

    <attribute type="EmpID" />
    <attribute type="FName" />
    <attribute type="LName" />
</ElementType>
</Schema>

Now, annotations are added to this XDR schema to map its elements and attributes to the database tables and columns. This is the annotated XDR schema:

<?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="Employee" sql:relation="Employees" >
    <AttributeType name="EmpID" />
    <AttributeType name="FName" />
    <AttributeType name="LName" />

    <attribute type="EmpID" sql:field="EmployeeID" />
    <attribute type="FName" sql:field="FirstName" />
    <attribute type="LName" sql:field="LastName" />
</ElementType>
</Schema>

In the mapping schema, the <Employee> element is mapped to the Employees table using sql:relation annotation. The attributes EmpID, Fname, and Lname are mapped to the EmployeeID, FirstName, and LastName columns in the Employees table using the sql:field annotations.

This annotated XDR schema provides the XML view of the relational data. This XML view can be queried using the XPath (XML Path) language. The query returns an XML document as a result, instead of the rowset returned by the SQL queries.

Note In the mapping schema, the specified relational values (such as table name and column name) are case-sensitive.

previous page start next page