Mastering Details with Flat Files
By Antony Reynolds on May 11, 2009
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:
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
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”).
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:
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"
- 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.
- 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:element name="Items" maxOccurs="1">
<xsd:element name="Item" nxsd:startsWith="L," maxOccurs="unbounded">
This gives us a native schema format that looks like this:
Note that this is the structure that we were aiming for in the first place.
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.
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.
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!