Pages

Wednesday, December 3, 2008

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.

94 comments:

  1. Hi I have been trying to do create this proxy but we dint create and business service or any thing and in the routing table it is asking for the service to be invoked which service do we need to specify????

    Do we create some business service??
    can you help me with this?

    ReplyDelete
  2. Hi

    you need to have an endpoint of the proxy service. You transform text to xml and then?. So you have to make a file adapter business service which put the output on a disc and select this bs in the routing table.

    thanks Edwin

    ReplyDelete
  3. Hi Edwin thanks for the reply but i am still unable to create this proxy service without problems i have created a proxy service with messaging service as the service type then specified the mfl file we created in this example as the request message type. response type as none.
    1.) In the transport i was not sure what to chose is it http/jms or anything else??
    I chose jms and the default end point as jms://localhost:7001/weblogic.jms.XAConnectionFactory/JmsRequest

    left the jms tranport/ message content and security tabs to defaults
    and then specified the message flow as explained by you

    Can you explain the file adapter business service specific settings ?

    ReplyDelete
  4. Hi,

    can you take a look at this
    http://biemond.blogspot.com/2008/10/oracle-service-bus-103.html

    and here an example how to create a jms queue http://biemond.blogspot.com/2008/10/jms-in-jdeveloper-11g-and-weblogic-103.html

    thanks

    ReplyDelete
  5. Hi biemond..
    The thing is like I was trying out with this example with a csv file.
    like
    1,xyz
    2,yyy
    3,www

    I was able to test the MFL and was able to transform the non-xml to xml. The problem is in the Query transformation part.

    I couldn't get all the elements in the source side(when referring to non-xml data). Is there any thing that I need to do other that this??

    ReplyDelete
  6. Hi, can you send me the testcase
    and let me know what the result should be.

    biemond at gmail dot com

    thanks

    ReplyDelete
  7. Hi Edwin,

    I made whole service and trying to test . i am literally confused what to pass as input payload is it flat file data or xml. I think it should be flat file data as we made proxy service based on mfl. Please guide me.

    ReplyDelete
  8. Hi,

    your flat file is the input. the proxy service knows this and translate this to xml and then pass it on as xml

    thanks Edwin

    ReplyDelete
  9. What type of transport i need is it http or jms? i selected http and
    When i post flat file i get following error
    Failure while unmarshalling message: Failed to transform MFL content from binary to XML

    Please help.

    ReplyDelete
  10. Hi

    I get the same error as above "Failure while unmarshalling message: Failed to transform MFL content from binary to XML".
    I am using a flat file (csv), transport is simple file.
    In test mode of the sbconsole i can see only an empty body as first line in the invocation trace - the error handler...??

    ReplyDelete
  11. Hi Edwin,
    I have a requirement to pick data from flat file and push it into a JMSQueue on Weblogic server. My question is

    Can I just use file protocol to pick the data from the file and push it into the JMSQueue

    or do I need to convert the flat file into xml using MFL as you have mentioned and achieve this?

    ReplyDelete
  12. Hi,

    You can use the messaging option in a file proxy server and pass this on to a jms business service without any transformations.

    thanks Edwin

    ReplyDelete
  13. If you are getting this error : Failed to transform MFL content from binary to XML,

    Start a new MFL and open it in the text editor and the change the version to 2.02 and build it. Refresh the MFL before opening in the XQuery editor for changes to reflect with the nodes.

    Thanks,
    Arun

    ReplyDelete
  14. Hi Edwin, I've a question regarding the treatment or control of errors in the MFL. So if the flat file is not well formed. Lets say that the first record 10 is: 10;0123456789;MR;JOHNSMITH

    The result for the Firstname tag will be: <Firstname>JOHNSMITH</Firstname> and the result for the Lastname tag will be: <Lastname>20;0123456789;ACCT1</Lastname>

    Do there is a way to treat or control this in the creation of the MFL? What about the indispensable \n at the final of the flat file because without it the transformation fails.

    ReplyDelete
  15. Hi,

    Good question, I think it is the same as xquery, maybe you only get an exception when it totally fails, like the input is not there else mfl always works or only the output is empty.

    so the best way to do some schema validation after the mfl transformation.

    hope this helps.

    ReplyDelete
  16. thanks a lot for a very useful post.
    I have a question though, how was the group account configured ?

    I have a requirement, where based on the input data, decision has to be made and rest of the row categorized.

    ReplyDelete
  17. chandan g (notifychandan@yahoo.com)November 18, 2009 at 7:33 AM

    Hi Edwin,

    I have a requirement, where based on the input data, decision has to be made and rest of the row categorized.

    I see that you have used it similarly with your example above.
    Can you please help me with the "account" created above in MFL?

    thanks,
    Chandan.

    ReplyDelete
  18. hi,

    here you got the mfl file

    <?xml version='1.0' encoding='windows-1252'?>
    <!DOCTYPE MessageFormat SYSTEM 'mfl.dtd'>
    <!-- Enter description of the message format here. -->
    <MessageFormat name='Personel' version='2.01'>
    <StructFormat name='Customer' delimOptional='n' repeat='*'>
    <TagField type='String' value='10;'/>
    <FieldFormat name='CustomerId' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='Gender' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='Firstname' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='Lastname' type='String' delimRef='' delim='\n' delimOptional='n' codepage='windows-1252'/>
    <StructFormat name='Account' delimOptional='n' repeat='*'>
    <TagField type='String' value='20;'/>
    <FieldFormat name='CustomerId' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='AccountT' type='String' delimRef='' delim='\n' delimOptional='n' codepage='windows-1252'/>
    <StructFormat name='Transactions' delimOptional='n' repeat='*'>
    <TagField type='String' value='30;'/>
    <FieldFormat name='CustomerId' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='Description' type='String' delimRef='' delim=';' delimOptional='n' codepage='windows-1252'/>
    <FieldFormat name='Amount' type='String' delimRef='' delim='\n' delimOptional='n' codepage='windows-1252'/>
    </StructFormat>
    </StructFormat>
    </StructFormat>
    </MessageFormat>

    thanks edwin

    ReplyDelete
    Replies
    1. Hi Edwin,

      I have created one similar MFL for converting CSV to XML. The result I am getting is inconsistent. Same file works and next run it fails.

      Only difference in the MFL is, I have added additional attribute called dataDelim='"' as I have to ignore optional delimiter.

      Input:

      H,"e1","e2"
      L,"le1","le2","le31,le32","le4"
      L,"le5","le6","le7","le81,le82"
      H,"e3","e4"
      L,"le9","le10,"le11,"le12"
      L,"le131,le132","le14","le15","le16"

      MFL:




















      Am I missing anything here or do we have any bug in MFL?

      Thanks
      Ramana.

      Delete
    2. MFL is missing in my previous update:
















      Delete
    3. Hi,

      MFL can be very tricky especially with the last line I always end with a CR LF
      Can you use soa suite?

      Thanks

      Delete
  19. Thanks Edwin. It was very helpful.

    Frankly I believe a tutorial on MFL is very much needed.

    Thanks again Edwin,
    Chandan.

    ReplyDelete
  20. Hi DO you have any sample build on this,pls share with me.

    Thanks,
    Narasimha.

    ReplyDelete
  21. Hi Edwin,
    Do you have an idea what I should to do when there is no data for a field (delimiter is not present too)?

    Thanks in advanced

    ReplyDelete
  22. Hi,

    Do you have a testcase then i will take a look .

    send it to biemond at gmail dot com

    thanks

    ReplyDelete
  23. Hi Edwin, it's very useful.
    anyway I already success generate xml after transformation, let's say like this :

    00010


    but now I need to transform the message like this :

    00010


    How can I do this?

    Really thanks

    Mr.K

    ReplyDelete
  24. Hi,

    there are more ways to add a attribute to a element like
    Message name="Investment"

    just open the xquery in source mode and do it manually or change the xsd schema and add the element and in the xquery wizard give this attribute a value.

    thanks

    ReplyDelete
  25. Hi

    rohan here,
    I want to i convert JSON req to XML response.
    what will be more feasible ,ie to use MFL or to use Java CallOut??

    JSON in is vvv large...

    ReplyDelete
  26. Hi,

    if i was you , i would use a java callout, like I did in this blogpost
    http://biemond.blogspot.com/2009/05/osb-rest-service-with-xml-json-output.html

    thanks

    ReplyDelete
  27. Hi Edwin,

    Apologies for lengthy query :

    I am doing a POC in which I am reading a csv file and writing to another csv file.
    I am following these steps:
    1. configure a proxy service with messaging as service type
    2. read the csv file using file protocol
    3. once the file comes to osb, transform the file to target schema using a xsl transformation in assign.
    (I need this intermediate schema because i have to use XSL instead of XQuery here and xsl will only accept xsd)
    4. use MFL transform to convert this to the target csv format.
    5. invoke business service which writes the file using MFL as service type.

    I am able to read a file and transform it to the intermediate target schema.
    But during the MFL transform, the message is soemhow getting treated as binary.

    My input to MFL transform activity is a variable which contains following data: (checked using LOG)



    1
    a
    b
    10


    2
    c
    d
    20



    And I am providing the target MFL file as second parameter. But the output of this activity is :



    which returns a reference to the binary data. I am expecting something like 1,a,b,10
    When I give the above xml in format builder test page, it generates the expected delimited data.

    Could you please let me know where I am going wrong ?

    Thanks,

    Ketan

    ReplyDelete
  28. Hi,
    The problem got solved somehow. I was trying out different things and something worked. I think OSB internally converts it to binary and provides a reference, but it also is then writing a text(ASCII) file in outbound operation(coz LOG is still showing binary ref entry.)

    Thanks for the wonderful blog !

    Regards,

    Ketan

    ReplyDelete
  29. Hello Edwin

    I'm starting with OSB, I am having problems when I try to create the xml schema. Could you please post the XML schema code?
    It would be very useful.

    Thank you

    ReplyDelete
  30. Hi,

    here you have the project , with the mfl, xquery and the sample data http://www.sbsframes.nl/jdeveloper/TextToXml.rar

    Good Luck.

    ReplyDelete
  31. Thanks for the blog. I am trying to send the xml to a file transport business service but the file being written is an empty file. I am getting a proper xml output in invocation trace when testing.

    ReplyDelete
  32. Hi Edwin,

    I was wondering if there is a way to throttle the input file in OSB. I would like to convert the plain text file into xml file and pass it to another business service, but I would like to throttle it. Is it possible to acheive it?

    Thanks

    ReplyDelete
  33. Hi,

    you probably have an empty body, did you assign the body variable.

    thanks

    ReplyDelete
  34. Hi,

    I think throttle with a input file in OSB is possible with the JCA file adapter and not with the internal file transport.

    hope this helps

    ReplyDelete
  35. Biemond,

    It will be really helpful if you can do the viceversa using format builder like converting xml to cobol copybook etc.

    ReplyDelete
  36. This comment has been removed by the author.

    ReplyDelete
  37. Hi Edwin,
    Thanks for your very useful blog.
    I have an OSB proxy which reads from a JMS topic and then uses MFL to convert the read message into XML with each line being tagged by ‘nativeString’. In MFL I use ‘/n’ as the delimiter which converts an input message of:
    Line1
    Line2
    Line3
    Into XML of

    Line1
    Line2
    Line3

    However when the input message is read, it is wrapped in SOAP which removes any final carriage returns. This means that MFL is unable to find the end of the last line with ‘/n’ and errors saying the whole message cannot be processed.
    To get around this I am reading the message in as text and then concatenating a carriage return ‘
    ’ before doing an MFL transform.
    I was wondering if there is a better way of doing this as I have heard it is better to do MFL as the request type if I am using MFL rather than using text and appending a carriage return before using MFL transform on the string?
    Many thanks,
    Mark

    ReplyDelete
  38. Hi,

    maybe you can replace the linefeed with ; or ' before you put the message on a queue.

    I think it works differently in SOA Suite and there it uses XSLT, maybe you can use that. The SOA suite way will be the standard in the next version of SOA and OSB combined together

    thanks

    ReplyDelete
  39. Hi Edwin,
    Thanks for your comment before. I have changed the MFL, since each transaction line finishes with four question marks '????' I am now using these as tags for the End Of Line.
    However, MFL is trimming any preceeding spaces before the end of line marker when it transforms it to XML
    i.e. if my input is as follows
    1234 67890 ?????
    123456 89101X????
    The output XML will have the end tag next to the 0 on the first line (not after the 3 end spaces as desired) and next to the X on the second line.
    I wish to keep the trailing spaces so it finishes with e.g.
    ... 7890 TAG
    and not
    ... 7890TAG
    Is it possible for MFL to keep the trailing spaces when it coverts non-XML to XML.
    Many thanks,
    Mark

    ReplyDelete
  40. Hi,

    I see there is a trim option on the fields, maybe this is on (default).

    but I can understand most frameworks trim spaces or do a number conversion.

    thanks

    ReplyDelete
  41. Hi, Edwin.

    I'm trying your published project from the download above, and
    I get the same error as above "Failure while unmarshalling message: Failed to transform MFL content from binary to XML".
    I am using a flat file (csv), transport is simple file.
    According your .mfl file, 2.01 version is not the problem, and 2.02 version doesn't help, as of mine.

    So, please tell us,

    1. why we get the error mentioned
    2. what is the maximum file size for the conversion

    Regards,

    Garry

    ReplyDelete
  42. Hi,

    what version of OSB did you use. I will re-test in PS4.

    Don't think there is a file size limit. It depends of your Weblogic memory.

    thanks

    ReplyDelete
  43. Oracle Service Bus Version: 11.1.1.3

    Regards,
    Garry

    ReplyDelete
  44. Hi,

    it works in 2.0.1 and 2.0.2 but when you test it in the sbconsole make sure after the copy paste of the testdata you shoud add an extra linefeed at the end of the testdata.

    else indeed you will get Failure while unmarshalling message: Failed to transform MFL content from binary to XML".

    thanks

    ReplyDelete
  45. Thank you for this great blog entry, I have been searching for just that for a while now.
    I have slighty different requirement. My group indetifier happens to be in the middle of the row.
    Like column 15 of 20.
    Is that still possible to use MFL?
    here is a sample:
    field1,field2,field3,field4,field5,field6,field7,field8,,field10,field11,indicator1,field13,field14,field15,field16,field17,field18
    field1,field2,field3,field4,field5,field6,field7,field8,,field10,field11,indicator2,field13,field14,field15,field16,field17,field18,field19,field20,field21,field22,field23,field24,field25

    Not sure how I would create the break.
    Any help is appreciated.

    ReplyDelete
    Replies
    1. Hi Steve, Were you able to achieve this ? I am also trying to do the same thing ..kindly share .Thanks

      Delete
  46. Hi Steve ,

    That should work. Any thing can be your record or field separator.

    Good luck.

    ReplyDelete
  47. hi Edwin,

    so i have been trying to the reverse operation i.e Convert xml to EDI format.
    i have created an mfl action referencing the mfl file.
    the EDI format is then supposed to write into a directory and .txt file.Ofcourse the Business service is configured to use mfl message type service
    on testing the proxy service with a sample input xml. i get no errors but the text file seems to have nothing. Obv the mfl transformation is returning a null result set. Any ideas what could i be doing wrong??

    another issue i find is that when i create an Xquery with the mfl file as the source target no nodes come up at all. its a null element. could there be any issue?

    ReplyDelete
  48. If I want convert a xml variable to a flat file.

    where to put the filenamepath value in?

    and

    I tried a lot of times.

    Nothing output.

    ReplyDelete
  49. where to put the file name and path?
    If I want to output a xml content to a flat text file?

    ReplyDelete
  50. Hi,

    you should use a file transport and maybe use a transport action and set the filename property.

    thanks

    ReplyDelete
  51. Hi Edwin,

    I have a issue with converting csv to xml file. I am able to get the result but the output result is different in ALSB 3.0 when compared with OSB 11.1.1.5.

    Input CSV Data:

    FName,LName
    Raj,Raj
    Ramu,Ranga

    Output from ALSB 3.0:

    <Root>
    <Address>
    <FName>Raj</FName>
    <LName>
    Ram
    </LName>
    </Address>
    <Address>
    <FName>Ramu</FName>
    <LName>
    Ranga
    </LName>
    </Address>
    </Root>

    Output from 11.1.1.5:

    <Root>
    <Address>
    <FName>Raj</FName>
    <LName>Ram </LName>
    </Address>
    <Address>
    <FName>Ramu</FName>
    <LName>Ranga </LName>
    </Address>
    </Root>

    Is there any difference in parser implementation with these two versions?

    Can you please point me if I am missing any thing.

    Thanks
    Ram.

    ReplyDelete
    Replies
    1. Hi,

      Sorry, but the PS4 looks good right. maybe in alsb it contains a bug.

      thanks

      Delete
    2. Thanks and yes looks like the extra line issue is resolved in PS4 but I see extra space at the end of the word. Like extra space after "Ranga "

      <LName>Ranga </LName>

      Do I need to modify any thing in the MFL File?

      My MFL File looks like this.

      <?xml version='1.0' encoding='windows-1252'?>
      <!DOCTYPE MessageFormat SYSTEM 'mfl.dtd'>
      <!-- Enter description of the message format here. -->
      <MessageFormat name='Root' version='2.01'>
      <StructFormat name='Address' delimOptional='y' repeat='*'>
      <FieldFormat name='Fname' type='String' delimRef='' delim=',' delimOptional='n' codepage='windows-1252'/>
      <FieldFormat name='LName' type='String' delimRef='' delim='\n' delimOptional='n' codepage='windows-1252'/>
      </StructFormat>
      </MessageFormat>

      Thanks
      Ram.

      Delete
  52. Can MFL read record one by one?
    I mean that :
    If the flat file is very big(1.5G), The MFL will waste too much memory to read all the records .

    Is there any way to handle the records one-by-one?

    ReplyDelete
    Replies
    1. Hi,

      you should use the jca file adapter, this can read chunks of data or use ODI.

      thanks

      Delete
    2. So the MFL can only load all the file data once. Will that change in next version? thanks a lot.

      Delete
    3. Hi,

      I think in the next version maybe MFL will be replaced by Oracle Native Format Builder or Oracle B2B.

      it more that the file transport does this. with the jca file adapter you can read chunks , don't know how this exactly works with OSB or MFL

      thanks

      Delete
  53. Hi Edwin
    Do you have nXSD (JCA File Adapter) created for this file layout used in this blog. If so can you share it here in this blog. ?

    Regards
    Sesha

    ReplyDelete
    Replies
    1. Hi,

      Everything is on https://github.com/biemond/osb11g_examples/tree/master/TextToXml

      and I don't use the file trasnport or the jca file transport.

      thanks

      Delete
  54. Hi Edwin,

    In my project i am trying to convert non-xml to xml... in .mfl file i had fields id,name,age,DateofBirth. At last .mfl file is working fine but when i was debug my proxyservice it is prompting an following error : Failure while unmarshalling message: Failed to transform MFL content from binary to XML... Can you help me out in this, it helps me a lot

    Regards >> >> >> Uday

    ReplyDelete
    Replies
    1. Hi,

      can you check the data input file and check for carriage returns, even on the last line.
      mfl may need this.

      thanks

      Delete
  55. Hi Edwin,

    When i was trying to import .cpy file by using MFL(Cobol copybook importer) it is giving an error like
    D:\HCR1.cpy: 1: error: Improper level number '0'; 01 assumed
    D:\HCR1.cpy: 1: error: Expected an identifier or FILLER, found ':'
    D:\HCR1.cpy: 1: error: Recovering, skipping to next '.'
    D:\HCR1.cpy, Lines: 79, Errors: 4, Warnings: 0

    what would be the Solution... Please let me know it helps me a lot...

    Thanks in advance
    Uday

    ReplyDelete
    Replies
    1. Hi,

      Does it works in the MFL tester. please check your CR LF in the input document.

      thanks

      Delete
  56. Hi Edwin,

    I have a similar requirement, the file is a flat file. it has a multiple occurring element. this element is not been recognized and generated only once . can u pleae help me

    ReplyDelete
    Replies
    1. Hi,

      you mean from xml to flat file , can you check the group or field occurrence.

      thanks

      Delete
  57. Hi Edwin
    i have created simple .mfl with two fields name and age,if i test in format builder its working fine.
    my.mfl file
    -----------











    my flat file
    ------------
    chandu,25

    xml file
    ---------



    chandu
    25



    upto now evrything is fine.if same code is deployed in OSB console i am getting below error

    Binary to XML mfl transformation failed for the MFL Resource FileInBoundProject/Example : Did not completely process all input data.

    Did not completely process all input data.


    ErrorCode
    -3


    DataOffset
    0




    Please help in this regard

    ReplyDelete
    Replies
    1. Hi,

      try yo add some extra linefeeds especially on your last line.

      thanks

      Delete
  58. Can you take a look at my post and let me know if what I am doing here is even possible, I can't seem to get it to work: http://www.mraandrews.co.uk/?p=91#more-91

    ReplyDelete
  59. Hi ,

    I am trying to build the MFL for the below format but no luck.
    Could you please help me in building the MFL .

    10/03/2012|1|4
    82601300|||8260130020207927||09/24/2012|10/09/2012|10/04/2012|11/03/2012|426.14
    10/03/2012|1|1693
    82601400|||8260140010007401||09/25/2012|10/15/2012|10/03/2012|11/02/2012|81.75
    82601400|||8260140010007666||09/25/2012||10/03/2012|11/02/2012|957.55
    82601400|||8260140010012302||09/24/2012||10/02/2012|11/01/2012|443.98
    82601400|||8260140010012385||09/25/2012|10/15/2012|10/03/2012|11/02/2012|153.62
    82601400|||8260140010014811||09/25/2012||10/03/2012|11/02/2012|755.93
    82601400|||8260140010015230||09/24/2012||10/02/2012|11/01/2012|571.95
    82601400|||8260140010029025||09/24/2012|10/14/2012|10/02/2012|11/01/2012|139.45
    82601400|||8260140010032565||09/25/2012||10/03/2012|11/02/2012|843.6
    82601400|||8260140010033720||09/24/2012||10/02/2012|11/01/2012|817.9
    82601400|||8260140010037754||09/25/2012||10/03/2012|11/02/2012|431
    82601400|||8260140010042192||09/25/2012||10/03/2012|11/02/2012|431.1
    82601400|||8260140010042358||09/25/2012||10/03/2012|11/02/2012|634.65
    82601400|||8260140010053546||09/25/2012|10/15/2012|10/02/2012|11/01/2012|142.56
    82601400|||8260140020013555||09/24/2012||10/02/2012|11/01/2012|391.4
    82601400|||8260140020041119||09/24/2012||10/01/2012|10/31/2012|375.22
    10/03/2012|1|23
    86221500|||8027410100001307||09/24/2012|10/16/2012|10/03/2012|11/02/2012|0
    86221500|||8027410100001315||09/24/2012|10/16/2012|10/03/2012|11/02/2012|0
    10/03/2012|1|11
    86222400|||8027400100002165||09/25/2012|10/17/2012|11/04/2012|12/03/2012|0
    86222400|||8027400100002199||09/25/2012|10/17/2012|11/05/2012|12/04/2012|0


    Regards
    Sandhya

    ReplyDelete
    Replies
    1. Hi,

      this is hard, there are probably 2 type of records but they don't have a fixed beginning of a row, like 10 or 20
      and the separator changes.

      so you can better pass it on to a java callout and do some string actions in java and return a xml.

      thanks

      Delete
  60. Hi,
    I am using MFL transform and converting it from xml to fixed length message but as soon as i replace body with the fixed length message then the trailing spaces are removed and i need these spaces. How to achieve this.

    Regards,
    Nishith

    ReplyDelete
    Replies
    1. Hi,

      can you try to escape the data and have the elements string as data types.
      thanks

      Delete
  61. Hi Edwin,
    My mfl test works fine.. later during xquery transformation I didn't get the fields listed in non XML source type. Since the fields are not listed I couldn't do the mapping with the schema file. Kindly guide me regarding this.

    Thanks,
    Tamil

    ReplyDelete
  62. Hi,
    I am sorry to reply to such an old post, but the Oracle support team is taking far too long to respond.

    the trim trailing character feature does not work for many people:

    https://forums.oracle.com/forums/thread.jspa?threadID=2251182

    Searching for the Format Builder patch returned no results.

    My SR returned no results either.

    Is there a filename or any other tip available for me to search the internet?

    Where and how can I find the patch?
    TIA!

    ReplyDelete
  63. why do we need to read csv file in OSB using MFL?
    can't we do it through XSD instead of MFL as we do in Oracle SOA.

    MFL is again defining the CSV file structure like fieldname and datatype.
    Can you please comment on this. I am confused between MFL and XSD.

    ReplyDelete
    Replies
    1. Hi,

      you can use the JCA File adapter of Jdeveloper in OSB. this uses XSD. MFL was already there before Oracle bought Bea.

      thanks

      Delete
  64. Thanks for sharing knowledge with us Edwin.

    I just need an information on mfl. We have a complex node to repeat from 0 to 5(not more than that). But when I set repeat='*', it means, minOccurs is 5 as well as maxOccurs also as 5. Is there any alternative that I have missed?

    Thanks in advance,
    Vikash

    ReplyDelete
  65. Sorry, there was a typo, it should be repeat='5'.

    ..Vikash

    ReplyDelete
  66. Hi,

    I am facing an issue in MFL transformation. For new line we are using “/n” in attribute values. When we are testing it in MFL itself, it is working fine. But when we are using it from web service it is not working.
    e.g
    Non XML:
    a,b
    c,d
    e,f
    g,h
    I want it like



    a
    b


    c
    d


    e
    f


    g
    h



    Thank You
    AR

    ReplyDelete
  67. Hi Edwin,
    do you know if is possible to read the body, sender and subject of emails and process the data included in that?
    I'm facing with that with no success.
    Any suggestions?

    Thanks.


    I

    ReplyDelete
    Replies
    1. Hi,

      Can you read the transport element of the inbound variable, this contains all the data.

      Thanks

      Delete
  68. I have a requirement to covert a binary data(will get binary data from JCAPS) to XML format through MFL transformation.

    Please hlep me out of this .

    Thanks

    ReplyDelete
    Replies
    1. Hi,

      MFL is not the right tool for binary data, for this you can do a java callout to change it to text or directly to xml.

      Thanks

      Delete
  69. is there anyone knows how to read xlsx file from oracle service bus without using JAVA callout ?

    ReplyDelete
    Replies
    1. HI, Have a look on this post ..... http://java.net/projects/oracleservicebus1031/downloads/download/INTEGRATING-EXCEL-WITH-OSB.zip

      Delete
  70. Hi Edwin-

    Great post as always. I am trying below using MFL , please let me know if this looks fine . Its not working :

    1) Business service - invoking webservice returning CSV data (Input : None , Output : MFL)
    2) Proxy service invoking the Business service , captures resposnse and applies the Xquery for MFL.

    Its always failing with error " BEA-382030: Failure while unmarshalling message: Failed to transform MFL content from binary to XML"

    3) I have tested the MFL with data and it works fine.

    Do you see any designing issues.

    Thanks
    SAM

    ReplyDelete
  71. Hi Edwin,

    i am reading a text file using file transport of proxy service. which later uses MFL transform action to convert text to xml using the MFL created for this.When I run MFL, it runs absolutely fine , but when i run end to end, the file once read, it is failing at MFL transformation step.Upon further investigation, i found that when i am placing the file with text data in inbound file location and open it, i found that cursor is at first line.It has to be at the last line for MFL to process successfully.Any solution you could provide to resolve the issue.

    Thanks and Regards,
    Rishu

    ReplyDelete
  72. Hi Guys,

    Can you please help me on publishing a large size xml request (around 100-200 mb) to JMS queue through Oracle Service Bus.
    Presently I am using a Business service to publish the request but while executing it gives unexpected error occur message. Please help me ... Thanks in advance

    ReplyDelete
  73. Hello Edwin,
    How do we go about doing the reverse i.e. Converting XML messages into CSV files.
    Is it possible using MFL. Also is it possible using the JCA adapter.
    How is it done?

    ReplyDelete
  74. Edwin:
    In the second assign we will create a new body and pass this to the body variable.
    when I run a test I got the error below (Please advise):
    <Exception on TransportManagerImpl.receiveMessage, com.bea.wli.sb.context.BindingLayerException: Failed to set the value of context variable "body". Value must be an instance of {http://schemas.xmlsoap.org/soap/envelope/}Body.
    com.bea.wli.sb.context.BindingLayerException: Failed to set the value of context variable "body". Value must be an instance of {http://schemas.xmlsoap.org/soap/envelope/}Body.

    ReplyDelete