X

Welcome to All Things Data Integration: Announcements, Insights, Best Practices, Tips & Tricks, and Trend Related...

ODI 12c - XML improvements

Alex Kotopoulis
Director of Product Management

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.

xsd:union

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:simpleType>
      <xs:union memberTypes="sizeNum sizeString" />
   </xs:simpleType>
</xs:element>

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

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

 Example XML fragment:

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

Resulting
ODI datastore:

xsd:list

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"/>
</xs:simpleType>

Example XML fragment:

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

Resulting ODI datastore:

substitutionGroup

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:sequence>
      <xs:element name="name" type="xs:string"/>
   </xs:sequence>
</xs:complexType>

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

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

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

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

Example XML Fragment:

<shoe>
   <name>Clydesdale</name>
<size>9.5</size>
</shoe>

<pants>
<name>Humdinger</name>
   <inseam>30</inseam>
   <waist>36</waist>
</pants>

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:sequence>
         <xs:element name="name" type="xs:string"/>
         <xs:element name="order" type="xs:positiveInteger"/>
         <xs:element name="date" type="xs:date"/>
      </xs:sequence>
   </xs:complexType>
</xs:element>

Example XML Fragment:

<email>
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>.
</email>

Resulting ODI datastore:

Annotations

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:annotation>
      <xs:appinfo>
         <version>17.1.4.1</version>
         <lastChange>2013-12-05</lastChange>
      </xs:appinfo>
      <xs:documentation>Formal customer record for retail clothing
orders.
</xs:documentation>
   </xs:annotation>
   <xs:complexType>
      <xs:sequence>
         <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"/>
      </xs:sequence>
   </xs:complexType>
</xs:element>

Example XML fragment:

<customer>
   <id>12321</id>
   <firstName>Estella</firstName>
   <lastName>Havisham</lastName>
   <birthDate>1961-12-18</birthDate>
</customer>

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:

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.