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.

Using XQuery in JDeveloper 11g and Workshop

With JDeveloper 11g you can use XQuery in your java code and test it with the XQuery runner of JDeveloper. But the only thing missing is a XQuery Designer. When Oracle bought Bea they also bought Workshop for Weblogic. This Workshop application is eclipse plugin which has a very nice XQuery designer. So let's combine these Oracle tools and make some awesome transformations.
First we create a new application / project in JDeveloper 11g with the following libraries.

Create a new xquery file in the project.

Start Workshop for Weblogic where we will create a new project and add a new XQuery Transformation.

Define the input of the XQuery. This can be a xml schema or something else. In my case I will use a xml schema. Workshop has now created a new input variable called data_message1.

My output is also a xml schema

Workshop provides a Expression functions window with all the XQuery functions you can use. Just drag a function to the target expression.

Here you have a overview of the XQuery mappings. Data is a anyType element which I map to the output schema

With the Workshop application you can also test the XQuery file by providing a input xml.

Now we can go back to JDeveloper where we will use this workshop XQuery and test this XQuery file. This is the original XQuery file from Workshop.

(:: pragma bea:global-element-parameter parameter="$data_message1" element="ns2:data-message" location="../xsd/mhs/main_mhs.xsd" ::)
(:: pragma bea:global-element-return element="ns1:data" location="../xsd/mhs/main_delfor.xsd" ::)

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
as element(ns1:data) {
<ns1:data RUNID = "{ xs:integer(data($data_message1/ns2:runid)) }"
SENDER = "{ data($data_message1/ns2:sender) }"
RECEIVER = "{ data($data_message1/ns2:recipient) }"
TIMESTAMP = "{ data($data_message1/ns2:timestamp) }">
<ns0:DELFOR>{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }</ns0:DELFOR>
</ns1:data>
};

declare variable $data_message1 as element(ns2:data-message) external;

xf:main_delfor($data_message1)

We have to change this XQuery file so it works in JDeveloper. We have to import the input schema else XQuery won't recognize this element(ns2:data-message) definition. To test the XQuery with the XQuery runner I need to change the external $data_message1 variable. This how the JDeveloper version looks like

import schema default element namespace "http://tennet.org/mhs/queue" at "main_mhs.xsd";

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
as element(ns1:data) {

{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }

};

declare variable $data_message2  := doc('delfor2.xml');
declare variable $data_message1  as element(ns2:data-message)  := $data_message2/data-message;

xf:main_delfor($data_message1)

In JDeveloper we can select the XQuery file and press run to see the output.

Off course we can run the xquery file in java too. We only have to use this class and make $data_message2 a external variable in the XQuery file

package nl.ordina.xquery;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;

import javax.xml.namespace.QName;

import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLParseException;
import oracle.xml.xqxp.datamodel.XMLItem;
import oracle.xml.xqxp.datamodel.XMLSequence;
import oracle.xquery.PreparedXQuery;
import oracle.xquery.XQueryContext;
import oracle.xquery.exec.Utils;
import org.xml.sax.SAXException;


public class TestXQuery {

public TestXQuery() {
try {

XQueryContext ctx = new XQueryContext();
Reader strm = new FileReader("main_delfor2.xq");
PreparedXQuery xquery = ctx.prepareXQuery(strm);

DOMParser prs = new DOMParser();
InputStream is = Utils.getStream("delfor2.xml");
prs.parse(is);
XMLDocument doc = prs.getDocument();

xquery.setNode( new QName("data_message2"), doc);

XMLSequence seq = xquery.executeQuery();
while (seq.next()) {
XMLItem item = seq.getCurrentItem();
item.getNode().print(System.out);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (XMLParseException e) {
e.printStackTrace();
} catch (SAXException e) {
e.printStackTrace();
}
}

public static void main(String[] args) {
TestXQuery testXQuery = new TestXQuery();
}
}

and this is the xquery I used for the java example

import schema default element namespace "http://tennet.org/mhs/queue" at "main_mhs.xsd";

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
as element(ns1:data) {

{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }

};

declare variable $data_message2  external;
declare variable $data_message1  as element(ns2:data-message)  := $data_message2/data-message;

xf:main_delfor($data_message1)

Here you can download the Jdeveloper project I used in this blog.