XML column mapping

If a database has been enabled for use with DB2 XML, table columns can be formatted
a mixture of regular DB2 data types and XML data types.

For this example, we’ve created a table with one column. The column is formatted
as an XMLCLOB data type using this SQL command:
CREATE TABLE OWNER.XMLONLY ( XMLDOC DB2XML.XMLCLOB NOT LOGGED ) ;

We’ll use the column to access book records as XML, rather than having to generate
and shred XML documents from relational data. We also create a very simple DAD
file, which will be used by the table to validate incoming XML documents and index
certain fields in the XML document for text searches. The dtdid element in the
example below tells DB2 XML Extender functions to validate incoming XML documents
against the AmazonListings DTD. The DAD elements that are nested inside
the Xcolumn element tell DB2 to extract and index the PRODUCTID and the TITLE
elements from XML documents for fast text searches. The element locations are
defined by XPath expressions.
<DAD>
<dtdid>AmazonListings</dtdid>
<validation>YES</validation>
<Xcolumn>
<table name=”XMLONLY_side_tab”>
<column name=”PRODUCTID” type=”integer”
path=”/AMAZONLISTINGS/PRODUCTID” multi_occurrence=”NO”/>
<column name=”TITLE” type=”CHARACTER(200)”
path=”/AMAZONLISTINGS/TITLE” multi_occurrence=”NO”/>
</table>
</Xcolumn>
</DAD>
The DTD reference is based on a unique ID. DB2 databases that have been enabled
for XML Extender store DTDs in a table called DTD_REF with a unique IDs. A reference
in a DAD file to a unique ID returns the matching DTD. Here’s the DTD:
<?xml version=”1.0” encoding=”UTF-8”?>
<!ELEMENT AMAZONLISTINGS (PRODUCTID, RANKING, TITLE, ASIN, AUTHORID, IMAGE,
SMALL_IMAGE, LIST_PRICE, RELEASE_DATE, BINDING, AVAILABILITY, TAGGED_URL)>
<!ELEMENT PRODUCTID (#PCDATA)>
<!ELEMENT RANKING (#PCDATA)>
<!ELEMENT TITLE (#PCDATA)>
<!ELEMENT ASIN (#PCDATA)>
<!ELEMENT AUTHORID (#PCDATA)>
<!ELEMENT IMAGE (#PCDATA)>
<!ELEMENT SMALL_IMAGE (#PCDATA)>
<!ELEMENT LIST_PRICE (#PCDATA)>
<!ELEMENT RELEASE_DATE (#PCDATA)>
<!ELEMENT BINDING (#PCDATA)>
<!ELEMENT AVAILABILITY (#PCDATA)>
<!ELEMENT TAGGED_URL (#PCDATA)>

The DTD is very basic, just specifying the elements that should be present in the
XML document, and the order in which they should be structured.

Posted in: XML