Sunday Dec 08, 2013

ODI 12c - XML improvements

ODI has added support to a number of advanced XML Schema constructs in the latest 12.1.2 release: union, list, substitution groups, mixed content, and annotations. XML is a core exchange format in SOA and B2B environments; it is essential for data integration to read and write XML documents in a data load process. ODI supports XML files as sources and targets and allows access to nested XML structures by mapping them to relational tables. This is accomplished through the ODI XML JDBC driver. This entry focuses on the new 12.1.2 features that build on top of XML support from previous ODI releases. 

Let’s take a look at the improvements in detail with concrete examples for each type.


The union construct allows a simple type to be based on a union of multiple different simple types, for example a clothing size that is either numeric or based on words. ODI maps a simple type based on union into a VARCHAR column in the resulting relational table, containing the XML string representation of the simple type.

Example XSD:

<xs:element name="shirtSize">
      <xs:union memberTypes="sizeNum sizeString" />

<xs:simpleType name="sizeNum">
<xs:restriction base="xs:positiveInteger">
      <xs:maxInclusive value="42"/>

<xs:simpleType name="sizeString">
<xs:restriction base="xs:string">
      <xs:enumeration value="small"/>
      <xs:enumeration value="medium"/>
      <xs:enumeration value="large"/>

 Example XML fragment:

<shirtSize >41</shirtSize>
<shirtSize >small</shirtSize>
<shirtSize >medium</shirtSize>

Resulting ODI datastore:


The list construct defines an XML Schema simple type that consists of a list of values delimited by space. ODI will load a list type into a VARCHAR column regardless of XML type and retain the space-separated value. The user would need to use SQL functions in ODI to extract the appropriate values from the list string.

Example XSD:

<xs:element name="availableDates" type="valuelist"/>

<xs:simpleType name="valuelist">
   <xs:list itemType="xs:integer"/>

Example XML fragment:

<availableDates>1 4 5 6 8 11 19 23 30</availableDates>

Resulting ODI datastore:


The XML Schema substitution group mechanism helps to extend a complex type into one or more subtypes. Elements of the supertype can be of any of the types in the substitution group, allowing polymorphism. In our example this allows to create a list that contains both shoes or pants types with different structures. ODI treats sequences based on substitution group elements much like it treats xsd:choice based sequences; it generates different tables for each substitution group member and loads the elements into the respective tables. Order and parent FK are maintained.

Example XSD:

<xs:element name="clothing" type="ClothingType"/>

<xs:complexType name="ClothingType">
      <xs:element name="name" type="xs:string"/>

<xs:element name="shoe" type="ShoeType" substitutionGroup="clothing"/>

<xs:complexType name="ShoeType">
      <xs:extension base="ClothingType">
            <xs:element name="size" type="xs:string"/>

<xs:element name="pants" type="PantsType" substitutionGroup="clothing"/>

<xs:complexType name="PantsType">
      <xs:extension base="ClothingType">
            <xs:element name="inseam" type="xs:integer"/>
            <xs:element name="waist" type="xs:integer"/>

Example XML Fragment:



Resulting ODI datastores:

Mixed Content

Mixed content allows an element to have arbitrary text between the child elements, much like XHTML. In the example the element email has acomplex type with attribute mixed=”true”. Because of this, ODI will map the element into a single VARCHAR column containing the entire markup with child elements and text. If the user needs to get to individual content inside this element, an external parser needs to be used.

Example XSD:

<xs:element name="email">
<xs:complexType mixed="true">
         <xs:element name="name" type="xs:string"/>
         <xs:element name="order" type="xs:positiveInteger"/>
         <xs:element name="date" type="xs:date"/>

Example XML Fragment:

Dear <name>Estella Havisham</name>!
We have processed your oder number <order>345424356</order>
The estimated date of delivery will be <date>2013-12-23</date>.

Resulting ODI datastore:


XML Schema annotations are used in XML Schema to provide metadata for schema constructs. Annotations have been allowed in ODI XML JDBC driver before, but they have been discarded. Now annotations on elements and attributes are stored in the ODI datastore description. Other annotations, such as those referring to the entire schema or to types, groups, sequences, and others, are still discarded. This information can be viewed and modified in ODI Studio and by the SDK, but modifications are not written back to the XSD file.

Example XSD:

<xs:element name="customer">
      <xs:documentation>Formal customer record for retail clothing orders.</xs:documentation>
         <xs:element name="id" type="xs:integer"/>
         <xs:element name="firstName" type="xs:string"/>
         <xs:element name="lastName" type="xs:string"/>
         <xs:element name="birthDate" type="xs:date"/>

Example XML fragment:


Annotation as datastore description:

The following are great resources if you need more information on ODI’s XML support in general: 

The files for this example can be downloaded here:


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« December 2013 »