• Article

Introduction to Annotated XDR Schemas (Deprecated in SQLXML 4.0)

Important

This topic is included as a reference for legacy applications. No future development work will be done on this feature. Avoid using this feature in new development work. Instead, use annotated XSD schemas to create XML views. For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). You can convert existing annotated XDR schemas to XSD schemas. For more information, see Converting Annotated XDR Schemas to Equivalent XSD Schemas (SQLXML 4.0).

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 a view 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 was first introduced to create XML schemas. At that time, the XDR was flexible and overcame some of the limitations of Document Type Definitions (DTDs), which also can be used to describe XML 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 data content is 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 the 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 first introduced a number of annotations that you could use to map the XDR schema to SQL 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. 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.

Using a copy of a schema to make annotations.

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.

SQL Server 2000 introduced 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 following example shows 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 a <Contacts> element and the CID, 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="Contacts" >
    <AttributeType name="CID" />
    <AttributeType name="FName" />
    <AttributeType name="LName" />

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

Now, annotations are added to this XDR schema to map its elements and attributes to the names of SQL tables and columns in the AdventureWorks2008R2 sample database. 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="Contacts" sql:relation="Person.Person" >
    <AttributeType name="CID" />
    <AttributeType name="FName" />
    <AttributeType name="LName" />

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

In the mapping schema, the <Contacts> element is mapped to the Person.Persontable using sql:relation annotation. The attributes CID, FName, and LName are mapped to the BusinessEntityID, FirstName, and LastName columns in the Person.Person 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.