Decomposition annotation example: A value mapped to a single table that yields a single row

Mapping a value from an XML document to a single table and column pair is a simple form of mapping in annotated XML schema decomposition. This example shows the simpler case of a one to one relationship between values in a rowSet.

The result of this mapping depends on the relationship between items mapped to the same rowSet. If the values that are mapped together in a single rowSet have a one to one relationship, as determined by the value of the maxOccurs attribute of the element or the containing model group declaration, a single row will be formed for each instance of the mapped item in the XML document. If the values in a single rowSet have a one to many relationship, where one value appears only once in the document for multiple instances of another item, as indicated by the value of the maxOccurs attribute, then multiple rows will result when the XML document is decomposed.

Consider the following XML document:

<publications>
  <textbook title="Programming with XML">
    <isbn>0-11-011111-0</isbn>
    <author>Mary Brown</author>
    <author>Alex Page</author>
    <publicationDate>2002</publicationDate>
    <university>University of London</university>
  </textbook>
</publications>

If you wanted the values of the <isbn> and <publicationDate> elements, as well as the title attribute, to be decomposed into the TEXTBOOKS table as follows, you need to add annotations to the declarations for these elements and attributes in the corresponding XML schema document. The annotations would specify the table and column names that each item is mapped to.

Table 1. TEXTBOOKS
ISBN	TITLE	DATE
0-11-011111-0	Programming with XML	2002

To map a value to a single table and column pair, you need to specify the table and column on the value that you want to map. Do that by using one of the following sets of annotations:

db2-xdb:rowSet and db2-xdb:column as attributes of <xs:element> or <xs:attribute>
<db2-xdb:rowSet> and <db2-xdb:column> as child elements of <db2-xdb:rowSetMapping>

The following annotated XML schema specifies the db2-xdb:rowSet and db2-xdb:column annotations as attributes.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
		xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">		
	
 <xs:annotation>
 	<xs:appinfo>
 	<db2-xdb:defaultSQLSchema>"MYSCHEMA"</db2-xdb:defaultSQLSchema>
 	</xs:appinfo>
 </xs:annotation>
 		
 <xs:element name="publications">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="textbook" maxOccurs="unbounded">
        <xs:complexType>
          <xs:sequence>
            <xs:element name="isbn" type="xs:string" maxOccurs="1" 
                        db2-xdb:rowSet="TEXTBOOKS" 
                        db2-xdb:column="ISBN"/> 
            <xs:element name="author" type="xs:string" maxOccurs="unbounded"/>
            <xs:element name="publicationDate" type="xs:gYear" maxOccurs="1" 
                        db2-xdb:rowSet="TEXTBOOKS" 
                        db2-xdb:column="DATE"/>
			<xs:element name="university" type="xs:string" maxOccurs="unbounded"/>
		  </xs:sequence>
	 	<xs:attribute name="title" type="xs:string" use="required" 
	 	              db2-xdb:rowSet="TEXTBOOKS" 
	 	              db2-xdb:column="TITLE"/> 
	    </xs:complexType> 
	  </xs:element> 
    </xs:sequence> 
  </xs:complexType> 
 </xs:element>
</xs:schema>

The maxOccurs attribute for the elements that go into the rowset is 1, so the items that are mapped to the TEXTBOOKS table have a one-to-one relationship with each other. Therefore, a single row is formed for each instance of the <textbook> element.