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:

Friday May 10, 2013

ODI 11g – XML Model Expert Accelerator

The blog post here provided a model creation accelerator for relational oriented technologies, I have uploaded another one for XML oriented data. The expert can be found on the site here.

This expert greatly simplifies getting up and running with XML in ODI, well I think so anyway. It asks the basic questions, whats the file name... then allows additional configuration if you want. You can skip advanced and external database storage and you will have a model with the topology objects built, you can get right into reverse engineering the datastores.

The tool tips come straight from the ODI documentation.

If the advanced option was selected, after pressing OK above, you will be shown some common advanced properties, you can change these and these will be used when building the URL for the XML data server (see full doc here). The actual root element in your document will be displayed in the root element entry.

The external database dialog has details of the relational system you wish to use. This has the basic properties, there are lots more potential configuration options, my goal here is to illustrate the expert which will get you up and running. Enter the ODI encoded password.

Once you have done this you can reverse your model....very simple! 

The expert is written in groovy using the ODI SDK and SwingBuilder which is very simple to use for creating these types of accelerators, let us know what you think.

Tuesday Apr 16, 2013

ODI - Integrating social data from Facebook Graph API in 0 to 60

I'm always on the lookout for enabling technology to push the envelope. Here is a viewlet illustrating how to integrate Facebook  data into your flow. It uses a preprocessor that will help you integrate JSON in general into your data integration world.

There are a few pieces to the jigsaw. ODI provides an XML driver that can be used to process XML data from the filesystem, http or ftp for example. ODI has improved this greatly by leveraging Oracle standard XML schema and parsing components. Transforming JSON to XML is one approach which is convenient for when you already have XML based tools or utilities. With this utility you can load JSON data into your data integration flows and in the process understand more about the data (when we reverse engineer the JSON we get a schema). Using this we can integrate arbitrary JSON data such as social data feeds from Twitter, Facebook, Google, and aggregators like Datasift among many others.

Watch the viewlet to see it in action.

Wednesday Mar 13, 2013

ODI - Tip of the day, processing RSS from the web

ODI can easily process RSS data from the filesystem or web...the ODI XML technology has a driver whose file specification includes support for filesystem, URL and ftp. So processing RSS feeds is very simple. The reverse engineer can also generate a DTD based on the XML, so you don't even need a DTD or XML schema for the input stream. Converting JSON to XML and reversing is a very simple way to reverse a schema for information such as JSON, you can then enrich the model in ODI. In the URL below which I defined for an XML dataserver, I specify a BBC news RSS feed, I can then easily reverse engineer the schema into ODI.

  • jdbc:snps:xml?f=

When you use the selective reverse engineer you can see the available datastores in the model generated, its very simple;

To be able to access this URL from within the ODI studio I did define my web proxy and URL - from within the ODI Studio UI you do this in the Tools->Preferences Web Browser and Proxy tab, I defined the HTTP proxy and port number to use.

Once reverse engineered you can view the model in an ODI diagram and see the relationships and objects reversed. You can also get to work and build interface sand view the data from the feed. For example, the ITEM datastore has the meat of the RSS news feed, here is a snippet of the content right now;

 Unlike file based XML, the HTTP based ones are read only, but let you easily integrate XML oriented datastreams such as RSS very easily.


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


« July 2016