db2-xdb:table decomposition annotation

<db2-xdb:table> belongs to the set of decomposition annotations that can be added to an XML schema document to describe the mappings between elements and attributes of XML documents to DB2® base tables. The decomposition process uses the annotated XML schema to determine how elements and attributes of an XML document should be decomposed into DB2 tables.

Annotation type

Global child element of <xs:appinfo> (which is a child element of <xs:annotation>)

Namespace

http://www.ibm.com/xmlns/prod/db2/xdb1

Valid structure

Whitespace within the character content of the children elements of <db2-xdb:table> is significant and not normalized. Content of these elements must follow the spelling rules for SQL identifiers. Undelimited values are case-insensitive; for delimited values, quotation marks are used as the delimiter. SQL identifiers that contain the special characters '<' and '&', must be escaped.

Details

Only base tables can be specified; other types of tables, such as typed, summary, temporary, or materialized query tables, are not supported for this mapping. Nicknames can be specified for DB2 Database for Linux, UNIX, and Windows data source objects only. Views and table aliases are not currently permitted for this annotation.

Example

The following example shows how the <db2-xdb:table> annotation can be used to group related elements and attributes together to form a row, when multiple location paths are being mapped to the same column. Consider first the following elements from an XML document (modified slightly from examples used for other annotations).

<root>
  …
  <book isbn="1-11-111111-1" title="My First XML Book">
    <authorID>22</authorID>
    <email>author22@anyemail.com</email>
    <!-- this book does not have a preface -->
    <chapter number="1" title="Introduction to XML">
      <paragraph>XML is fun...</paragraph>
      ...
    </chapter>
    <chapter number="2" title="XML and Databases">
      <paragraph>XML can be used with...</paragraph>
    </chapter>
    ...
    <chapter number="10" title="Further Reading">
      <paragraph>Recommended tutorials...</paragraph>
    </chapter>
  </book>
  …
  <author ID="0800" email="author800@email.com">
    <firstname>Alexander</firstname>
    <lastname>Smith</lastname>
    <activeStatus>0</activeStatus>
  </author>
  …
<root>

Assume that the purpose of this decomposition mapping is to insert rows that consist of author IDs and their corresponding email addresses into the same table, AUTHORSCONTACT. Notice that author IDs and email addresses appear in both the <book> element and the <author> element. Thus, more than one location path will need to be mapped to the same columns of the same table. The <db2-xdb:table> annotation, therefore, must be used. A section from the annotated schema is presented next, showing how <db2-xdb:table> is used to associate multiple paths to the same table.

<!-- global annotation -->
  <xs:annotation>
    <xs:appinfo>
      <db2-xdb:defaultSQLSchema>adminSchema</db2-xdb:defaultSQLSchema>
      <db2-xdb:table>
        <db2-xdb:SQLSchema>user1</db2-xdb:SQLSchema>
        <db2-xdb:name>AUTHORSCONTACT</db2-xdb:name>
        <db2-xdb:rowSet>bookRowSet</db2-xdb:rowSet>
        <db2-xdb:rowSet>authorRowSet</db2-xdb:rowSet>
      </db2-xdb:table>
    </xs:appinfo>
  </xs:annotation>

  <xs:element name="book">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="authorID" type="xs:integer"
                    db2-xdb:rowSet="bookRowSet" db2-xdb:column="AUTHID" />
        <xs:element name="email" type="xs:string"
                    db2-xdb:rowSet="bookRowSet" db2-xdb:column="EMAILADDR" />
        <xs:element name="chapter" type="chapterType" maxOccurs="unbounded" />
      </xs:sequence>
      <xs:attribute name="isbn" type="xs:string" />
      <xs:attribute name="title" type="xs:string" />
    </xs:complexType>
  </xs:element>

  <xs:element name="author">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="firstname" type="xs:string" />
        <xs:element name="lastname" type="xs:string" />
        <xs:element name="activeStatus" type="xs:boolean" />
      </xs:sequence>
      <xs:attribute name="ID" type="xs:integer"
                    db2-xdb:rowSet="authorRowSet" db2-xdb:column="AUTHID" />
      <xs:attribute name="email" type="xs:string"
                    db2-xdb:rowSet="authorRowSet" db2-xdb:column="EMAILADDR" />
    </xs:complexType>
  </xs:element>

The <db2-xdb:table> annotation identifies the name of the target table for a mapping with the db2-xdb:name child element. In this example, AUTHORSCONTACT is the target table. To ensure that the ID and email addresses from the <book> element are kept separate from those of the <author> element (that is, each row contains logically related values), the <db2-xdb:rowSet> element is used to associate related items. Even though in this example, the <book> and <author> elements are separate entities, there can be cases where the entities to be mapped are not separate and require a logical separation, which can be achieved through the use of rowSets.

Note that the AUTHORSCONTACT table exists in an SQL schema different from the default SQL schema, and the <db2-xdb:SQLSchema> element is used to specify this. The resulting AUTHORSCONTACT table is shown below:

This example illustrates how the logical grouping of values through rowSets ensure that unrelated values are not unintentionally mapped to the same table and column pair. In this example, /root/book/authorID and /root/author/@ID are mapped to the same table and column pair. Similarly, /root/book/email and /root/author/@email are mapped to the same table and column pair. Consider the case if rowSets were not available. If, for example, the /root/book/email element was not present in an instance of the <author> element and rowSets could not be used, then it would not be possible to determine if the email from the <author> element should be associated with /root/book/authorID or /root/author/@ID or both. Thus the rowSets associated with a single table in the <db2-xdb:table> annotation helps logically distinguish among different sets of rows.