DB2 Version 9.7 for Linux, UNIX, and Windows

rowSets in annotated XML schema decomposition

db2-xdb:rowSet identifies the target table into which a value is decomposed. This annotation can be set either to a table name or a rowSet name.

A rowSet is specified with the db2-xdb:rowSet annotation, which is added to the XML schema document as either an attribute of an element or attribute declaration, or a child of the <db2-xdb:rowSetMapping> annotation.

The set of mappings, across all schema documents that form the XML schema, which has the same db2-xdb:rowSet value for an instance of an element or attribute, defines a row.

For example, 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>
  <childrensbook title="Children's Fables">
    <isbn>5-55-555555-5</isbn>
    <author>Bob Carter</author>
    <author>Melaine Snowe</author>
    <publicationDate>1999</publicationDate>
  </childrensbook>
</publications>

To decompose this document such that each book's isbn and title (whether it is a textbook or children's book) is inserted into the same table (named ALLPUBLICATIONS), multiple rowSets must be defined: a rowSet to group values related to textbooks, and another to group values related to children's books.

rowSets, in this case, ensure that only values that are semantically related are grouped together to form a row. That is, the use of rowSets will group the isbn value for a textbook with its title, and the isbn value for a children's book with its title. This ensures that a row does not contain the isbn value from a textbook, while having the title from a children's book.

Without rowSets, it is impossible to determine which values should be grouped together to form a row that is still semantically correct.

The application of rowSets in an XML schema document is presented next. The two rowSets, textbk_rowSet and childrens_rowSet, are specified on the isbn element declaration of the <textbook> and <childrensbook> elements respectively. These rowsets are then associated with the ALLPUBLICATIONS table through the <db2-xdb:table> annotation.

Note that using the rowSet annotation not as a table identifier, but as a rowSet identifier allows you to easily change table names referenced in the XML schema. This is because, when the value of db2-xdb:rowSet represents an identifier rather than a table name, you need to use the <db2-xdb:table><db2-xdb:name></db2-xdb:name></db2-xdb:table> annotation to actually specify the table name. With this method, you need to update the table name in only one place, if required.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1"
           elementFormDefault="qualified" attributeFormDefault="unqualified">
  <xs:annotation>
    <xs:appinfo>
      <db2-xdb:defaultSQLSchema>admin</db2-xdb:defaultSQLSchema>
      <db2-xdb:table>
        <db2-xdb:name>ALLPUBLICATIONS</db2-xdb:name>
        <db2-xdb:rowSet>textbk_rowSet</db2-xdb:rowSet>
        <db2-xdb:rowSet>childrens_rowSet</db2-xdb:rowSet>
      </db2-xdb:table>
    </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"
                       db2-xdb:rowSet="textbk_rowSet" db2-xdb:column="PUBS_ISBN"/>
                <xs:element name="author" type="xs:string" maxOccurs="unbounded"/>
                <xs:element name="publicationDate" type="xs:gYear"/>
                <xs:element name="university" type="xs:string"
                            maxOccurs="unbounded"/>
              </xs:sequence>
              <xs:attribute name="title" type="xs:string" use="required"
                      db2-xdb:rowSet="textbk_rowSet" db2-xdb:column="PUBS_TITLE"/>
            </xs:complexType>
          </xs:element>
          <xs:element name="childrensbook" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="isbn" type="xs:string"
                    db2-xdb:rowSet="childrens_rowSet" db2-xdb:column="PUBS_ISBN"/>
                <xs:element name="author" type="xs:string" maxOccurs="unbounded"/>
                <xs:element name="publicationDate" type="xs:gYear"/>
              </xs:sequence>
              <xs:attribute name="title" type="xs:string" use="required"
                   db2-xdb:rowSet="childrens_rowSet" db2-xdb:column="PUBS_TITLE"/>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
</xs:schema>

The table that results from decomposing using this annotated XML schema is shown next.

Table 1. ALLPUBLICATIONS
ISBN	PUBS_TITLE
0-11-011111-0	Programming with XML
5-55-555555-5	Children's Fables

While the example presented above shows a simple case of decomposing using rowSets, rowSets can be used in more complex mappings to group together multiple items from different parts of an XML schema to form rows on the same table and column pair.

Conditional transformations

rowSets allow you to apply different transformations to the values being decomposed, depending on the values themselves.

For example, consider the following two instances of an element named "temperature":

<temperature unit="Celsius">49</temperature>
<temperature unit="Fahrenheit">49</temperature>

If the values of these elements are to be inserted into the same table, and you want the table to contain consistent values (all Celsius values, for example), then you need to convert the values that have the attribute unit="Fahrenheit" to Celsius before inserting. You can do this by mapping all elements with the attribute unit="Celsius" to one rowSet and all elements with the attribute unit="Fahrenheit" to another rowSet. The rowSet for Fahrenheit values can then have a conversion formula applied before insertion.

Notice that the mapping on the attribute declaration of "unit" does not contain any db2-xdb:column specification. This means that value of the item will only be used for condition evaluation and not for storage into the table specified by the db2-xdb:rowSet specification.

The following XML schema document could be used to insert the Celsius and converted Fahrenheit values into the same table:

....
<!-- Global annotation -->
<db2-xdb:table>
  <db2-xdb:name>TEMPERATURE_DATA</db2-xdb:name>
  <db2-xdb:rowSet>temp_celsius</db2-xdb:rowSet>
  <db2-xdb:rowSet>temp_fahrenheit</db2-xdb:rowSet>
</db2-xdb:table>
...
<xs:element name="temperature">
  <xs:annotation>
    <xs:appinfo>
      <db2-xdb:rowSetMapping>
        <db2-xdb:rowSet>temp_celsius</db2-xdb:rowSet>
        <db2-xdb:column>col1</db2-xdb:column>
      </db2-xdb:rowSetMapping>
      <db2-xdb:rowSetMapping>
        <db2-xdb:rowSet>temp_fahrenheit</db2-xdb:rowSet>
        <db2-xdb:column>col1</db2-xdb:column>
        <db2-xdb:expression>
	  myudf_convertTocelsius($DECOMP_CONTENT)
	</db2-xdb:expression>
      </db2-xdb:rowSetMapping>
    </xs:appinfo>
  </xs:annotation>
  <xs:complexType>
    <xs:simpleContent>
      <xs:extension base="xs:int">
        <xs:attribute name="unit" type="xs:string">
          <xs:annotation>
            <xs:appinfo>
              <db2-xdb:rowSetMapping>
                <db2-xdb:rowSet>temp_celsius</db2-xdb:rowSet>
                <db2-xdb:condition>
		  $DECOMP_CONTENT = 'Celsius'
		</db2-xdb:condition>
              </db2-xdb:rowSetMapping>
              <db2-xdb:rowSetMapping>
                <db2-xdb:rowSet>temp_fahrenheit</db2-xdb:rowSet>
                <db2-xdb:condition>
		  $DECOMP_CONTENT = 'fahrenheit'
		</db2-xdb:condition>
              </db2-xdb:rowSetMapping>
            </xs:appinfo>
          </xs:annotation>
        </xs:attribute>
      </xs:extension>
    </xs:simpleContent>
  </xs:complexType>
</xs:element>