OIC - Read Multi records of different types CSV file | How to create a multi records of different types XSD to read CSV file

Use Case: We have multi Records are of Different types CSV file. How we can create a schema for the same and read via oracle integrarion.

File to be read: InputSourceData.txt

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice,header1, header2,header3,header4

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Line, Line1,Line2,Line3,Line4,Line5,Line6,Line7,Line8

Invoice Trailer, Trailer1

Notes: This case, we have following  3 types of records and 1st column values are fixed which would be our conditionValue while generating the xsd. >> 

1. Invoice (Header)
2. Invoice Line(Line)
3. Invoice Trailer(Trailer)

Implementation steps:

Step1: Create XSD from Jdeveloper tool

Create a SOA project and open composite.xml canvas and drag and drop a File adapter:

XSD is generated but while you tested the C1 or column 1 value for each record is missing due to have conditionValue attribute. Here we modify the code little to add the column 1 fixed or default values:

For each record add the below line and it works:

<xsd:element name="C1" type="xsd:string" default="Invoice"/>

The Modified XSD:

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

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">

<xsd:element name="Root-elememt">

<xsd:complexType>

<xsd:choice minOccurs="1" maxOccurs="unbounded" nxsd:choiceCondition="terminated" nxsd:terminatedBy=",">

<xsd:element name="RECORD1" nxsd:conditionValue="Invoice">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD2" nxsd:conditionValue="Invoice Line">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Line"/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C6" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C8" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;"/>

<xsd: element name="C9" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="RECORD3" nxsd:conditionValue="Invoice Trailer">

<xsd:complexType>

<xsd:sequence>

<xsd: element name="C1" type="xsd:string" default="Invoice Trailer "/>

<xsd: element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

Step2: Create a scheduled orchestration integration and drag and drop a FTP adpater connection and read the file using this created schema.

Activate and test.