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. Annotations 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.

Note:
In Microsoft SQLXML 4.0, the XSD schema language supports the annotations introduced with annotated XML-Data Reduced (XDR) schema language in SQL Server 2000. Annotated XDR is deprecated in SQLXML 4.0.

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.

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.

In the following example, the XSD schema consists of an <Person.Contact> element. The <Employee> element has a ContactID attribute and <FirstName> and <LastName> child elements:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Contact" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     type="xsd:string" /> 
        <xsd:element name="LName"
                     type="xsd:string" />
     </xsd:sequence>
        <xsd:attribute name="ConID" 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="Contact" sql:relation="Person.Contact" >
   <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="ConID" 
                       sql:field="ContactID" 
                       type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

In the mapping schema, the <Contact> element is mapped to the Person.Contact table in the sample AdventureWorks database by using the sql:relation annotation. The attributes ConID, FName, and LName are mapped to the ContactID, FirstName, and LastName columns in the Person.Contact 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.

Note:
In the mapping schema, case-sensitivity for the specified relational values (such as table name and column name) depends upon if SQL Server is using case-sensitive collation settings. For more information, see Using SQL Collations.

You can find more information about XML Schema Definition language (XSD), XML Path language (XPath), and Extensible Stylesheet Language Transformations (XSLT) at the following Web sites:

• XML Schema Part 0: Primer, W3C Recommendation (http://www.w3.org/TR/xmlschema-0/)
  
• XML Schema Part 1: Structures, W3C Recommendation (http://www.w3.org/TR/xmlschema-1/)
  
• XML Schema Part 2:Datatypes, W3C Recommendation (http://www.w3.org/TR/xmlschema-2/)
  
• XML Path Language (XPath) (http://www.w3.org/TR/xpath)
  
• XSL Transformations (XSLT) (http://www.w3.org/TR/xslt)
  

Reference

Help and Information