Defining XML Content with XSD Schemas | Beginning XML Databases (Wrox Beginning Guides)

XSD can be used to link a set of relational database tables in a schema to an XML document. XSD allows strong typing to be applied to XML data. The result is a typed XML data type where restrictive format and structure is applied to XML data. In other words, the XML document has a certain form and structure, restricted by the content of a schema the table and field structure of a schema.

Strongly Typing XML Documents with XSD

The definition or structure of a table in a relational database will describe data in that database. Similarly, XSD can be used to explicitly describe the structure of an XML document. The following XSD script defines a pseudocode XSD schema for regions in the demographics database:

   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <xsd:element name="  RegionRecord  " >       <xsd:complexType>          <xsd:sequence>             <xsd:element name="region_id" type="xsd:integer" />             <xsd:element name="region" type="xsd:string" />             <xsd:element name="population" type="xsd:integer" />             <xsd:element name="area" type="xsd:integer" />          </xsd:sequence>       </xsd:complexType>    </xsd:element> </xsd:schema>

To continue on with the preceding example, a hierarchical relational database table structure can be built. In the following pseudocode example, the REGION and COUNTRY tables are used. Countries are contained within each of their respective parent regions as a collection of countries , within each region:

   <?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">    <xsd:element name="RegionRecord">       <xsd:complexType>          <xsd:sequence>             <xsd:element name="region_id" type="xsd:integer" />             <xsd:element name="region" type="xsd:string" />             <xsd:element name="population" type="xsd:integer" />             <xsd:element name="area" type="xsd:integer" />             <xsd:element name="  CountryCollection  " type="  CountryRecord  " />          </xsd:sequence>       </xsd:complexType>    </xsd:element>    <xsd:element name="  CountryRecord  ">       <xsd:complexType>          <xsd:sequence>             <xsd:element name="region_id" />             <xsd:element name="country_id" type="xsd:integer" />             <xsd:element name="country" type="xsd:string" />             <xsd:element name="code" type="xsd:string" />             <xsd:element name="population" type="xsd:integer" />             <xsd:element name="area" type="xsd:integer" />             <xsd:element name="fxcode" type="xsd:string" />             <xsd:element name="currency" type="xsd:string" />             <xsd:element name="rate" type="xsd:float" />          </xsd:sequence>       </xsd:complexType>    </xsd:element> </xsd:schema>

Mapping an XSD Schema to a Table

The objective of using XSD is to allow generation of XML that is validated against a schema (set of related tables). Instead of writing queries to generate XML documents XSD can be used to map XML to tables in a schema. You can annotate XSD with special elements, allowing a direct mapping process to occur between XML and relational tables. By default, an element represents a table, and element attributes represent fields in tables. The result is a direct correlation between XSD annotations and relational tables (and their inter-relationships):

   <?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"    xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/sqltypes">  <xsd:element name="RegionRecord"  sql:relation="REGION"  >   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id"  sql:field="REGION_ID"  type="xsd:integer" />     <xsd:element name="region"  sql:field="REGION"  type="xsd:string" />     <xsd:element name="population"  sql:field="POPULATION"  type="xsd:integer" />     <xsd:element name="area"  sql:field="AREA"  type="xsd:integer" />     <xsd:element name="CountryCollection"  sql:relationship="CountryRecord"  />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:element name="CountryRecord"  sql:relation="COUNTRY"  >   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id"  sql:field="REGION_ID"  type="xsd:integer" />     <xsd:element name="country_id"  sql:field="COUNTRY_ID"  type="xsd:integer" />     <xsd:element name="country"  sql:field="COUNTRY"  type="xsd:string" />     <xsd:element name="code"  sql:field="CODE"  type="xsd:string" />     <xsd:element name="population"  sql:field="POPULATION"  type="xsd:integer" />     <xsd:element name="area"  sql:field="AREA"  type="xsd:integer" />     <xsd:element name="fxcode"  sql:field="FXCODE"  type="xsd:string" />     <xsd:element name="currency"  sql:field="CURRENCY"  type="xsd:string" />     <xsd:element name="rate"  sql:field="RATE"  type="xsd:float" />    </xsd:sequence>   </xsd:complexType>  </xsd:element> </xsd:schema>

Annotating the XSD Script to Enforce Relationships

Now annotations are added to both specify and enforce the hierarchical relationships between the tables:

   <?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"    xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/sqltypes">  <xsd:element name="RegionRecord" sql:relation="REGION">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />     <xsd:element name="region" sql:field="REGION" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />     <xsd:element name="CountryCollection" sql:relationship="CountryRecord" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:element name="CountryRecord" sql:relation="COUNTRY">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />     <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />     <xsd:element name="country" sql:field="COUNTRY" type="xsd:string" />     <xsd:element name="code" sql:field="CODE" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />     <xsd:element name="fxcode" sql:field="FXCODE" type="xsd:string" />     <xsd:element name="currency" sql:field="CURRENCY" type="xsd:string" />     <xsd:element name="rate" sql:field="RATE" type="xsd:float" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:annotation>   <xsd:appinfo>   <sql:relationship name="CountryRecord" parent="RegionRecord" parent-   key="region_id" />   </xsd:appinfo>   </xsd:annotation>  </xsd:schema>

Now lets add the STATE and CITY tables in the following XSD document:

   <?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/sqltypes">  <xsd:element name="RegionRecord" sql:relation="REGION">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />     <xsd:element name="region" sql:field="REGION" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />     <xsd:element name="CountryCollection" sql:relationship="CountryRecord" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:element name="CountryRecord" sql:relation="COUNTRY">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />     <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />     <xsd:element name="country" sql:field="COUNTRY" type="xsd:string" />     <xsd:element name="code" sql:field="CODE" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />     <xsd:element name="fxcode" sql:field="FXCODE" type="xsd:string" />     <xsd:element name="currency" sql:field="CURRENCY" type="xsd:string" />     <xsd:element name="rate" sql:field="RATE" type="xsd:float" />     <xsd:element name="StateCollection" sql:relationship="StateRecord" />     <xsd:element name="CityCollection" sql:relationship="CityRecord" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:element name="StateRecord" sql:relation="STATE">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="state_id" sql:field="STATE_ID" type="xsd:integer" />     <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />     <xsd:element name="state" sql:field="STATE" type="xsd:string" />     <xsd:element name="code" sql:field="CODE" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />     <xsd:element name="nickname" sql:field="NICKNAME" type="xsd:string" />     <xsd:element name="CityCollection" sql:relationship="CityRecord" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:element name="CityRecord" sql:relation="CITY">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="city_id" sql:field="CITY_ID" type="xsd:integer" />     <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />     <xsd:element name="city_id" sql:field="CITY_ID" type="xsd:integer" />     <xsd:element name="city" sql:field="CITY" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:annotation>   <xsd:appinfo>   <sql:relationship name="CountryRecord" parent="RegionRecord" parent-   key="region_id"   child= "CountryRecord" child-key="country_id" />   <sql:relationship name="StateRecord" parent="CountryRecord" parent-   key="country_id"   child= "StateRecord" child-key="state_id" />   <sql:relationship name="CityRecord" parent="CountryRecord" parent-   key="country_id"   child= "CityRecord" child-key="city_id" />   </xsd:appinfo>   </xsd:annotation>  </xsd:schema>

What an XSD script provides is an XML to relational table mapping. This is also sometimes known as an XML View such that relational table data can be directly mapped into an XML hierarchical structure, and viewed as an XML document.

Storing XSD as a Schema Collection

A SQL Server 2005 schema collection is a collection of definitions, of relational tables and fields, stored as an XSD script. In other words, a schema collection is an XSD script, stored as a definition in a SQL Server 2005 database:

   CREATE XML SCHEMA COLLECTION RegionalCollection AS  '<?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/sqltypes"> <xsd:element name="RegionRecord" sql:relation="REGION">  <xsd:complexType>   <xsd:sequence>    <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />    <xsd:element name="region" sql:field="REGION" type="xsd:string" />    <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />    <xsd:element name="area" sql:field="AREA" type="xsd:integer" />    <xsd:element name="CountryCollection" sql:relationship="CountryRecord" />   </xsd:sequence>  </xsd:complexType> </xsd:element> <xsd:element name="CountryRecord" sql:relation="COUNTRY">  <xsd:complexType>   <xsd:sequence>    <xsd:element name="region_id" sql:field="REGION_ID" type="xsd:integer" />    <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />    <xsd:element name="country" sql:field="COUNTRY" type="xsd:string" />    <xsd:element name="code" sql:field="CODE" type="xsd:string" />    <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />    <xsd:element name="area" sql:field="AREA" type="xsd:integer" />    <xsd:element name="fxcode" sql:field="FXCODE" type="xsd:string" />    <xsd:element name="currency" sql:field="CURRENCY" type="xsd:string" />    <xsd:element name="rate" sql:field="RATE" type="xsd:float" />    <xsd:element name="StateCollection" sql:relationship="StateRecord" />    <xsd:element name="CityCollection" sql:relationship="CityRecord" />   </xsd:sequence>  </xsd:complexType> </xsd:element> <xsd:element name="StateRecord" sql:relation="STATE">  <xsd:complexType>   <xsd:sequence>    <xsd:element name="state_id" sql:field="STATE_ID" type="xsd:integer" />    <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />    <xsd:element name="state" sql:field="STATE" type="xsd:string" />    <xsd:element name="code" sql:field="CODE" type="xsd:string" />    <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />    <xsd:element name="area" sql:field="AREA" type="xsd:integer" />    <xsd:element name="nickname" sql:field="NICKNAME" type="xsd:string" />    <xsd:element name="CityCollection" sql:relationship="CityRecord" />   </xsd:sequence>  </xsd:complexType> </xsd:element> <xsd:element name="CityRecord" sql:relation="CITY">   <xsd:complexType>    <xsd:sequence>     <xsd:element name="city_id" sql:field="CITY_ID" type="xsd:integer" />     <xsd:element name="country_id" sql:field="COUNTRY_ID" type="xsd:integer" />     <xsd:element name="city_id" sql:field="CITY_ID" type="xsd:integer" />     <xsd:element name="city" sql:field="CITY" type="xsd:string" />     <xsd:element name="population" sql:field="POPULATION" type="xsd:integer" />     <xsd:element name="area" sql:field="AREA" type="xsd:integer" />    </xsd:sequence>   </xsd:complexType>  </xsd:element>  <xsd:annotation>   <xsd:appinfo>    <sql:relationship name="CountryRecord" parent="RegionRecord"       parent-key="region_id"       child= "CountryRecord" child-key="country_id" />    <sql:relationship name="StateRecord" parent="CountryRecord"       parent-key="country_id"       child= "StateRecord" child-key="state_id" />    <sql:relationship name="CityRecord" parent="CountryRecord"       parent-key="country_id"       child= "CityRecord" child-key="city_id" />   </xsd:appinfo>  </xsd:annotation> </xsd:schema>' GO

Without applying the schema collection created previously, the following query uses its inherent query structure to determine the structure of the XML output:

   SELECT region.region AS name, region.population, region.area,    country.country AS name, country.currency,    state.state AS name, state.nickname,    city.city AS name FROM region JOIN country ON country.region_id = region.region_id    JOIN state ON state.country_id = country.country_id       JOIN city ON (city.country_id = country.country_id                     OR city.state_id = state.state_id) WHERE country.country = 'Canada' ORDER BY region.region, country.country, state.state, city.city FOR XML AUTO, ROOT, ELEMENTS GO

This is a partial result:

   <root>   <region>     <name>North America</name>     <population>331599508</population>     <area>18729272</area>     <country>       <name>Canada</name>       <currency>Dollars</currency>       <state>         <name>Alberta</name>         <nickname></nickname>         <city>           <name>Burlington</name>         </city>         <city>           <name>Calgary</name>         </city>         <city>           <name>Edmonton</name>         </city> ...       </state>     </country>   </region> </root>

Now apply the schema collection, and the schema collection called RegionalCollection determines the structure of the returned XML data:

   SELECT region.region AS name, region.population, region.area,    country.country AS name, country.currency,    state.state AS name, state.nickname,    city.city AS name FROM region JOIN country ON country.region_id = region.region_id    JOIN state ON state.country_id = country.country_id       JOIN city ON (city.country_id = country.country_id                     OR city.state_id = state.state_id) WHERE country.country = 'Canada' ORDER BY region.region, country.country, state.state, city.city FOR XML AUTO, XMLSCHEMA('RegionalCollection') GO

The result is shown here. At the top, the XSD definition of the schema collection is output (only a partial result of the XSD definition is shown). Following the XSD schema collection definition is the actual XML output, whose structure is determined by the XSD document, stored in the database as a schema collection. Effectively, a direct mapping has been created between relational tables and XML hierarchical structure using the XSD and schema collection definitions. Neat huh?

   <xsd:schema targetNamespace="RegionalCollection" xmlns:schema="RegionalCollection" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">   <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />   <xsd:element name="region">     <xsd:complexType>       <xsd:sequence> ... </xsd:schema> <region xmlns="RegionalCollection" name="North America" population="331599508" area="18729272">   <country name="Canada" currency="Dollars">     <state name="Alberta" nickname="">       <city name="Burlington" />       <city name="Calgary" />       <city name="Edmonton" />       <city name="Halifax" />       <city name="Montreal" />       <city name="Ottawa" />       <city name="Quebec City" />       <city name="Toronto" />       <city name="Vancouver" />     </state>     <state name="British Columbia" nickname="">       <city name="Burlington" />       <city name="Calgary" />       <city name="Edmonton" />       <city name="Halifax" />       <city name="Montreal" />       <city name="Ottawa" />       <city name="Quebec City" />       <city name="Toronto" />       <city name="Vancouver" />     </state>     <state name="Nova Scotia" nickname="">       <city name="Burlington" />       <city name="Calgary" />       <city name="Edmonton" />       <city name="Halifax" />       <city name="Montreal" />       <city name="Ottawa" />       <city name="Quebec City" />       <city name="Toronto" />       <city name="Vancouver" />     </state>     <state name="Ontario" nickname="">       <city name="Burlington" />       <city name="Calgary" />       <city name="Edmonton" />       <city name="Halifax" />       <city name="Montreal" />       <city name="Ottawa" />       <city name="Quebec City" />       <city name="Toronto" />       <city name="Vancouver" />     </state>     <state name="Quebec" nickname="">       <city name="Burlington" />       <city name="Calgary" />       <city name="Edmonton" />       <city name="Halifax" />       <city name="Montreal" />       <city name="Ottawa" />       <city name="Quebec City" />       <city name="Toronto" />       <city name="Vancouver" />     </state>   </country> </region>