DB2 Version 9.7 for Linux, UNIX, and Windows

db2-xdb:column decomposition annotation

The db2-xdb:column annotation specifies a column name of the table to which an XML element or attribute has been mapped.

db2-xdb:column 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

Attribute of <xs:element> or <xs:attribute>, or child element of <db2-xdb:rowSetMapping>

How to specify

db2-xdb:column is specified in any of the following ways (where value represents a valid value for the annotation):

<xs:element db2-xdb:rowSet="value" db2-xdb:column="value" />
<xs:attribute db2-xdb:rowSet="value" db2-xdb:column="value" />

<db2-xdb:rowSetMapping>
  <db2-xdb:rowSet>value</db2-xdb:rowSet>
  <db2-xdb:column>value</db2-xdb:column>
  …
</db2-xdb:rowSetMapping>

Namespace

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

Valid values

Any base table column name that adheres to the following:

Undelimited column names are case-insensitive. For delimited column names, escape the delimiter with ". For example, to specify a two word column name, "col one", db2-xdb:column would be set as follows:
```
db2-xdb:column="&quot;col one&quot;"
```
(Note that these conditions are requirements specific to this annotation.)
Only columns of the following data types can be specified in this annotation: all data types supported by the CREATE TABLE SQL statement, except user-defined structured types.

Details

The db2-xdb:column annotation, specified as an attribute in the declaration of an XML element or attribute, or as a child element of <db2-xdb:rowSetMapping>, maps an XML element or attribute to the column name of a target table. When this annotation is used, the db2-xdb:rowSet annotation must be specified as well. Together they describe the table and column that will hold the decomposed value for this element or attribute.

Example

The following example shows how content from the <book> element can be inserted into columns of a table called BOOKCONTENTS, using the db2-xdb:column annotation. A section of the annotated schema is presented first.

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

<xs:complexType name="chapterType">
  <xs:sequence>
    <xs:element name="paragraph" type="paragraphType" maxOccurs="unbounded"
                db2-xdb:rowSet="BOOKCONTENTS"
                db2-xdb:column="CHPTCONTENT" />
  </xs:sequence>
  <xs:attribute name="number" type="xs:integer"
                db2-xdb:rowSet="BOOKCONTENTS"
                db2-xdb:column="CHPTNUM" />
  <xs:attribute name="title" type="xs:string"
                db2-xdb:rowSet="BOOKCONTENTS"
                db2-xdb:column="CHPTTITLE" />
</xs:complexType>
  
<xs:simpleType name="paragraphType">
  <xs:restriction base="xs:string"/>
</xs:simpleType>

The <book> element that is being mapped is presented next, followed by the resulting BOOKCONTENTS table after the decomposition has completed. .

<book isbn="1-11-111111-1" title="My First XML Book">
  <authorID>22</authorID>
  <!-- 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>

Table 1. BOOKCONTENTS
ISBN	CHPTNUM	CHPTTITLE	CHPTCONTENT
1-11-111111-1	1	Introduction to XML	XML is fun...
1-11-111111-1	2	XML and Databases	XML can be used with...
…	…	…	…
1-11-111111-1	10	Further Reading	Recommended tutorials...