To create an XML view with annotated XDR schemas

xml| Create | schema | view

You can use the XDR (simplified xml-data) schema to create an XML view of relational data. You can then use XPath queries to query these views. This is similar to creating a view using the CREATE VIEW statement and specifying a SQL query for the views.

An XML schema describes the structure of an XML document and the different constraints on the data in the document. When an XPath query for the schema is specified, the returned XML document structure is determined by the schema on which the XPath query is performed.

In Microsoft®sql server™2000, the schema is created using the simplified Xml-data (XDR) language. XDR is a flexible language that overcomes some of the limitations of the document type definition (DTD) that is used to describe the document structure. Unlike DTDs, the XDR schema describes the document structure using the same syntax as the XML document. In addition, in the DTD, all data content is character data. The XDR language schema allows you to specify the data type of an element or attribute.

The,<schema> element in the XDR schema contains the entire schema. As a property of the <Schema> element, you can describe the attributes that define the schema name and the namespace in which the schema resides. In the XDR language, all element declarations must be contained within the <Schema> element.
The smallest XDR schema is as follows:
<?xml version= "1.0"?>
<schema xmlns= "Urn:schemas-microsoft-com:xml-data" >
...
</Schema>
<Schema> elements are derived from the Xml-data namespace (urn:schemas-microsoft-com:xml-data).

This document assumes that you are familiar with the Xml-data language.
Comments for XDR schemas
You can query the database by using annotations that describe the mapping to the database in the XDR schema and return the results in an XML document format. SQL Server 2000 introduces a number of annotations that you can use to map an XDR schema to tables and columns in a database. You can specify an XPath query for the XML view created by the XDR schema to query the database and get the results in XML format.
This is a way of replacing the more complex SQL query writing process, which uses the FOR XML EXPLICIT pattern to describe the XML document structure as part of the query. For more information about using the for XML EXPLICIT pattern in a SELECT query, see Using EXPLICIT mode. However, to overcome most of the restrictions on XPath queries on the mapped schema, use the SQL query for XML EXPLICIT mode to return the results of the XML document format.
If you have a common XDR schema, such as a Microsoft biztalk™ schema, you can do any of the following:
· Write a query for XML EXPLICIT mode so that the generated data is valid for the public XDR schema; however, writing a FOR XML EXPLICIT query can be cumbersome.
· Make a private copy of the public XDR schema. The annotation is then added to the private clone to generate the mapping schema. You can specify an XPath query for the mapping schema. The query generates data from the public schema namespace. Creating a annotated schema and specifying an XPath query to the schema is a much simpler process than writing a complex for XML EXPLICIT query. The following figure illustrates this procedure.


Explains that the Microsoft biztalk™ framework is designed to define standard XML formats for common business objects, such as contracts, orders, and appointments. Copies of these business structures can be found in http://biztalk.org/BizTalk/default.asp.
Mapping schemas
In the context of a relational database, it is useful to map any XDR schema to relational storage. One way to achieve this is to annotate the XDR schema. The annotated XDR schema, called the mapping schema, provides information about how to map XML data to relational storage. The mapping schema is actually an XML view of relational data. You can use these mappings to retrieve relational data in an XML document format.
Microsoft SQL Server 2000 introduces a number of annotations that you can use in the XDR schema to map elements and attributes to database tables and columns. You can use XPath (XML path) to specify a query to the mapping schema (XML view). The mapping schema describes the resulting document structure.
namespace for annotations
In the XDR schema, use the following namespace to specify the annotation: Urn:schemas-microsoft-com:xml-sql.
The following example shows that the simplest way to specify a namespace is to specify it in the <Schema> tag. Annotations of the Urn:schemas-microsoft-com:xml-sql namespace must be qualified by a 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 used is arbitrary. In this document, the SQL prefix is used to represent annotation namespaces and to make annotations in this namespace distinct from annotations in other namespaces.
Namespaces for data types
The XDR schema allows you to specify the data type of an element or attribute. Specify the data type using the following namespaces: Urn:schemas-microsoft-com:datatypes.
The following are the minimum XDR schemas with 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 used is arbitrary. In this document, the DT prefix is used to represent a data type namespace and to make annotations in this namespace distinct from annotations in other namespaces.
<Schema> elements are derived from the Xml-data namespace: Urn:schemas-microsoft-com:xml-data.
XDR Schema Sample
The following example shows how to add annotations to an XDR schema. The XDR architecture consists of <Employee> elements and EmpID, Fname, and Lname features.
<?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 add annotations to this XDR schema so that the elements and attributes of the schema are mapped to the tables and columns of the database. The XDR schema with annotations is as follows:
<?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 this mapping schema, the <Employee> element is mapped to the Employees table using the sql:relation annotation. Use the Sql:field annotation to map attributes EmpID, Fname, and Lname to Employees, EmployeeID, and FirstName columns in the LastName table.
This annotated XDR schema provides an XML view of relational data. You can query the XML view using the Xpath (XML path) language. The Xpath query returns the results in the form of an XML document, rather than the rowset returned by the SQL query.

Description in the mapping schema, the specified relationship value, such as table name and column name, is case-sensitive.