Mastering Details with Flat Files

The Problem

The native format builder wizard in the file adapter is great at reading flat file structures but doesn’t support reading more structured file structures.  Sometimes we need to read more complex structures such as master-detail records.  Let’s look at how we can use the file adapter to read structured file formats.

For example imagine a laundry list file such as the one below:

P,101,James
L,Shirt,2,Starch
L,Socks,6,De-odorise
L,Pants,2,Remove Stains
P,220,JoJo
L,Sweatshirt,1,Handwash
L,Shirt,3,No Iron
L,Socks,2,Iron
L,Pants,2,Steam Press
L,Tie,1,Dry clean
P,305,Ruth
L,Skirt,7,Iron Pleats
L,Socks,8,Iron

To make it easier to see the record structure I have put boxes around the individual records.  This can be represented in XML as shown below

image

The native format builder wizard would either treat each line as either the same type of record (“Multiple records are of single type”) or as two different types of record (“Multiple records are of different types”).

image

In the first case we would have a single record type with four fields that fails to distinguish between people (lines pre-fixed with ‘P’) and laundry items (lines pre-fixed with ‘L’).  This does not reflect the fact that the two lines are of different types.  The second case, multiple records are of different types, is closer to what we want.  It creates two records types, the type being determined by the value of the first field.  The XML for this is shown below:

image

However this case creates a list of two record types with no recognition that one record is nested inside the other.

Comparing the two XML representations we can see what needs to be done; we need to add an Items element under Room and move the Item element to be under Items.  The question is how do we describe this using the native schema constructs supported by the file adapter.

Creating a Master-Detail Records Native Format Schema

The easiest way to deal with this is to use the native format builder wizard in the file adapter to create the basic outline of the records for us.  We choose “Multiple records are of different types” and provide appropriate names for the two records types and individual fields within the records.

Having created the basic native format schema we can now edit it to be exactly the way we want it for the laundry list.

We make the following changes in the generated native schema file:

  • Replace the <choice> element by a <sequence> element.  In conjunction with other changes this will give us a list of elements of the same type, rather than of list of elements of two different types.
    • <xsd:choice minOccurs="1" maxOccurs="unbounded"
          nxsd:choiceCondition="terminated"
          nxsd:terminatedBy=","
      >
          …
      </xsd:choice>
      <xsd:sequence>
         …
      </xsd:sequence>
  • Replace the conditionValue attributes with startsWith attributes and add a comma to the end of the attribute values.  This will allow the native schema processor to identify the start of master records and child records.  We also add a maxOccurs attribute to the elements modified so that they can have multiple instances in a sequence.
    • <xsd:element name="Item"
          nxsd:conditionValue="L">

      </xsd:element>
      <xsd:element name="Item"
          nxsd:startsWith="L,"
          maxOccurs="unbounded"
      >

      </xsd:element>
      <xsd:element name="Room"
          nxsd:conditionValue="P">

      </xsd:element>
      <xsd:element name="Room"
          nxsd:startsWith="P,"
          maxOccurs="unbounded"
      >
          …
      </xsd:element>
  • Add an <Items> element as a sequence to the <Room> element sequence and move the <Item> element to be inside the <Items> sequence.

      <xsd:element name="Room" nxsd:startsWith="P," maxOccurs="unbounded">
          <xsd:complexType>
              <xsd:sequence>
                  …
                  <xsd:element name="Items" maxOccurs="1">
                      <xsd:complexType>
                          <xsd:sequence>
                              <xsd:element name="Item" nxsd:startsWith="L," maxOccurs="unbounded">
                                  …
                              </xsd:element>
                          </xsd:sequence>
                      </xsd:complexType>
                  </xsd:element>
              </xsd:sequence>
          </xsd:complexType>
      </xsd:element>

This gives us a native schema format that looks like this:

image

Note that this is the structure that we were aiming for in the first place.

Sample Code

I have created a simple BPEL process that performs the following steps

  • Read a laundry file using the wizard generated native schema format.  This creates an XML document with two distinct records types.
    • Note that in the process I do not delete the file so it is important to make sure that if you re-use the same file with the process you need to update its timestamp!
  • Read the laundry file again using the modified native schema format.  This creates an XML document with master-detail style records which reflects the actual format of the file.
    • Note that I use a synch read with the filename provided by the inbound header from the previous file read.   I delete the input file after reading it.
  • Write the laundry file using a pure XML schema to create an XML document file of the laundry list.

MasterDetailFileProcess

Sample code is zipped up as a JDeveloper project and can be downloaded here.  In addition to the normal BPEL artifacts within the project, I have included a sample laundry file (laundry.sample.txt) in the top level of the project directory.  After deploying to a BPEL server the process will look in C:\FileTransfer\InBound for the input file, after processing the input file it is written to C:\FileTransfer\Processed and the output file is generated in C:\FileTransfer\OutBound.  Either create appropriate directories or edit the project to use new directories.

Documentation

This entry has used the facilities of the native format schemas in SOA Suite.  These are documented in chapter 7 of the Oracle® Application Server Adapters for Files, FTP, Databases, and Enterprise Messaging User's Guide.

Good luck in using the adapters to process master-detail records!

Comments:

I stumbled upon this blogging by semi-accident but I must say you do a very good explaining the problem and the solution to it. Very good use of images to complement the text, extremely helpful entry.

Posted by guest on September 09, 2011 at 03:29 AM MDT #

Post a Comment:
Comments are closed for this entry.
About

Musings on Fusion Middleware and SOA Picture of Antony Antony works with customers across the US and Canada in implementing SOA and other Fusion Middleware solutions. Antony is the co-author of the SOA Suite 11g Developers Cookbook, the SOA Suite 11g Developers Guide and the SOA Suite Developers Guide.

Search

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