Export (0) Print
Expand All

Creating XML Views by Using Annotated XSD Schemas

SQL Server 2000

You can create XML views of relational data by using the XML Schema Definition (XSD) language. These views can then be queried by using XML Path language (XPath) queries. (This is similar to creating views by using CREATE VIEW statements and then specifying SQL queries against the view.)

An XML schema describes the structure of an XML document and also describes the 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 an XSD schema, the <xsd:schema> element encloses the entire schema; all element declarations must be contained within the <xsd:schema> element. You can describe attributes that define the namespace in which the schema resides and the namespaces that are used in the schema as properties of the <xsd:schema> element.

The minimum XSD schema is:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                    xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
...
</xsd:schema>

The <xsd:schema> element is derived from the XMLSchema namespace specification at http://www.w3.org/2001/XMLSchema.

Annotations to the XSD Schema

You can use an XSD schema with annotations that describe the mapping to a database, query the database, and return the results in the form of an XML document. Anotations are provided to map an XSD schema to database tables and columns. XPath queries can be specified against the XML view created by the XSD schema to query the database and obtain results as an XML.

In Microsoft® SQLXML 3.0, the XSD schema language supports the annotations introduced with  annotated XML-Data Reduced (XDR) schema language introduced in SQL Server 2000. For more information, see Using Annotations in XSD Schemas.

In the context of the relational database, it is useful to map the arbitrary XSD schema to a relational store. One way to achieve this is to annotate the XSD schema. An XSD schema with the annotations is referred to as a mapping schema, which provides information pertaining to how XML data is to be mapped to the 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.

Namespace for Annotations

In an XSD schema, annotations are specified by using the namespace urn:schemas-microsoft-com:mapping-schema. As shown in the following example, the easiest way to specify the namespace is to specify it in the <xsd:schema> tag.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
...
</xsd: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.

Example of an Annotated XSD Schema

In the following example, the XSD schema consists of an <Employee> element. The <Employee> element has an EmpID attribute and FName and LName child elements:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Employee" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     type="xsd:string" /> 
        <xsd:element name="LName"  
                     type="xsd:string" />
     </xsd:sequence>
        <xsd:attribute name="EmpID" type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Annotations are added to this XSD schema to map its elements and attributes to the database tables and columns:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Employee" sql:relation="Employees" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     sql:field="FirstName" 
                     type="xsd:string" /> 
        <xsd:element name="LName"  
                     sql:field="LastName"  
                     type="xsd:string" />
     </xsd:sequence>
        <xsd:attribute name="EmpID" 
                       sql:field="EmployeeID" 
                       type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

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

This annotated XSD schema provides the XML view of the relational data. This XML view can be queried using the XPath language. An XPath query returns an XML document as a result, instead of the rowset that is returned by SQL queries.

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

Annotated Schema Security Issues

Show:
© 2014 Microsoft