Monday Aug 05, 2013

ODI - Complex file example, multi record row

Here we will see how a parser is defined to project a schema based on a data file with many records in a single line in the file. The complex file adapter provides a plethora of capabilities and is probably one of those under utilized and little known components - plus I think its one that the more examples provided for it the better! This example I will show has a department defined on every line and each department can have many employees (as many as you want) defined on the same line. Replace Department and Employee for your own concepts; buyers, sellers, or whatever. We define the parser in an NXSD file, which is based on the standard XSD with additional information to define how the data is parsed and projected as XML. If you know XSD you are in business, if not, with a little knowledge you can become very dangerous - resort to the examples in the Oracle documentation if not.

My source data file has the following content;

ODIDavid  ARCHITECTBob    DEVELOPERJohn   MANAGER  
OWFFred   SUPPORT  Jim    SUPPORT  

I want to define a parser that creates departments for each line (ODI is a department and OWF is a department, its a 3 character string) and also has employees (David, Bob etc a 7 character string) with their job roles (ARCHITECT, DEVELOPER etc a 9 character string) and the department they are in.

The following NXSD file does exactly that;

  1. <?xml version="1.0" encoding="UTF-8" ?>

  2. <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  3.             xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd"
  4.             targetNamespace="http://TargetNamespace.com/testsch"
  5.             xmlns:tns="http://TargetNamespace.com/testsch"
  6.             nxsd:encoding="ASCII" nxsd:stream="chars" nxsd:version="NXSD">

  7.   <xsd:element name="Departments">
  8.     <xsd:complexType>
  9.       <xsd:sequence>
  10.         <xsd:element name="Department" type="tns:DepartmentT" maxOccurs="unbounded"/>
  11.       </xsd:sequence>
  12.     </xsd:complexType>
  13.   </xsd:element>

  14.   <xsd:complexType name="DepartmentT">
  15.     <xsd:sequence>
  16.         <xsd:element name="Name" type="xsd:string" nxsd:style="fixedLength" nxsd:length="3" />
  17.         <xsd:element name="Employee" type="tns:EmployeeT"  nxsd:style="array" maxOccurs="unbounded" nxsd:arrayTerminatedBy="${eol}" />
  18.     </xsd:sequence>
  19.   </xsd:complexType>

  20.   <xsd:complexType name="EmployeeT">
  21.          <xsd:sequence>
  22.            <xsd:element name="Name" type="xsd:string" nxsd:style="fixedLength" nxsd:length="7"/>
  23.            <xsd:element name="Role" type="xsd:string" nxsd:style="fixedLength" nxsd:length="9"/>
  24.          </xsd:sequence>
  25.   </xsd:complexType>

  26. </xsd:schema>

My employee records above happen to fixed length based on the length of the name and the role, there are many of them, so they are in an unbounded array and the array is terminated by an EOL character. When I reverse this into ODI I get datastores for DEPARTMENTS, DEPARTMENT and EMPLOYEE; EMPLOYEE has columns Name and Role, DEPARTMENT has column Name.

I can define this NXSD in my favorite editor and develop and test the parsing using ODI, this avoids installing and using JDeveloper and gives you a shortcut to quickly developing and building parsers using this capability. Previous blogs on the topic are here and here.

About

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

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today