Flat file to xml with Oracle Service Bus

With OSB you can easy transform a formatted flat file to a xml. To do this in OSB you have to create a MFL and XQuery definition. MFL is the output of the Format Builder tool where you can create descriptions of non XML files. XQuery can use the output of MFL to generate the xml.

This is an example of the flat file
10;0123456789;MR;JOHN;SMITH
20;0123456789;ACCT1
30;0123456789;SALARY;500000
30;0123456789;BONUS;1000
20;0123456789;ACCT2
30;0123456789;OTHER;100
10;1234566790;MR;DAVID;DOE
20;1234567890;ACCT1
30;1234567890;SALARY;10000

And this is the record definition of the flat file

one block is made of:
one record 10 (customer)
1 to N record 20 per record 10 (accounts)
1 to N record 30 per record 20 (transactions)

record 10
customer id
customer lastname
customer firstname

record 20
customer id
account id

record 30
customer id
label
value

Create a new MFL in the Oracle Workshop for Weblogic ( Eclipse plugin )

This will start Format builder tool where we can define the records groups and test the result.

First we add group 10 ( customer ) as child of the main element. Then we define the fields of recordtype 10. As child group of customer we add the Account group etc.

Here is a picture of the customer group where I enabled that the group is tagged option, with as value 10; and the group occurence is unlimited.
I add a child field which can occur once and has ; as delimiter

The last field of the record has as delimiter \n ( end of line )

Now we can test it ( menu tools and test ). Load the non xml data and transforms this to xml.

I made a new xml schema with I can use as target in Xquery. This is the xsd
Now we can create a new XQuery file where we can use the just created MFL definition as non xml source type and the xml schema as target type.


Let's map the source element to the targets element.
We are finished with XQuery and now we can create a proxy service. This proxy does the transformation and pass the xml output to an other service.

Create a new proxy service with messaging as service type. As Request message type we have to use the just created MFL.

Create a new message flow in this proxy service where we add 3 operations ( 2 assign and one insert )

In the first assign we will use the XQuey file and drag the personel in the body variable to the XQuery variable. Pass the output of this assign to the xml variable

In the second assign we will create a new body and pass this to the body variable

The last operation is insert, In this operation we copy the content of the xml variable to the body variable.

That's all now we have converted the flat file to a xml which we can passed on to other business services.