This discussion is archived
8 Replies Latest reply: May 30, 2013 6:19 AM by user100012345 RSS

How to load XML file to DB

1008841 Newbie
Currently Being Moderated
Hi All,
My requirement is I have a XML file, its data should be stored in Database.
Below is the sample XML file.
<?xml version="1.0"?>
<PaymentInfoMessageResponse>
<PaymentInfoResponse>
<TransactionType>940</TransactionType>
<SequenceNum>04</SequenceNum>
<CompanyCode>902</CompanyCode>
</PaymentInfoResponse>
<StatusCode>OK</StatusCode>
<StatusDetail>OK</StatusDetail>
<ResponseItemCount>2</ResponseItemCount>
</PaymentInfoMessageResponse>

I have a table with columns name say TransType,SeqNum
the value 940,04 from XML file should be saved in TransType,SeqNum Column respectively
I am using file adapter with native format builder,selecting file type as complex type and
In design schema,I'm not able to proceed,i'm getting error in POP up as:
Native Format builder:error
No global elements exist.

How can i proceed or any other way to load XML file to DB.
urgent Help/suggestion is required.

Thanks in advance
  • 1. Re: How to load XML file to DB
    Prabu Pro
    Currently Being Moderated
    if you mean to do it with SOA Suite or OSB you must use the Database JCA Adapters to do this.

    https://java.net/projects/oraclesoasuite11g/pages/DBAdapters
  • 2. Re: How to load XML file to DB
    veejai24 Pro
    Currently Being Moderated
    I think you have the schema type as opeque, you should create those column names with what ever the datatypes of the values are.

    http://www.orafmwschool.com/soa-suite-11g-mediator-example-implementation-part-3/

    The above example talks about an insert operation, which will help you to fix the issue. If you still have the problem share the schema file of the db adapter here.

    Thanks,
    Vijay
  • 3. Re: How to load XML file to DB
    NeerajSehgal Journeyer
    Currently Being Moderated
    Hi,

    You can't create the schema using Native Format Builder for XML files.

    - Create a schema (.xsd file) explicitly for your XML using Jdeveloper by clicking on
    File Menu > New > All Technologies tab > General > XML > XML Schema from XML Document.

    - After you have the schema created follow below post to process your file.
    http://yatanveersingh.blogspot.com/2010/06/reading-xml-file-using-file-adapter-in.html

    - Once you have the file content read in BPEL you can use DB Adapter to insert the records in the table.

    Let us know if you face any other issues afterwards.

    Regards,
    Neeraj Sehgal
  • 4. Re: How to load XML file to DB
    991448 Journeyer
    Currently Being Moderated
    Define a clob/blob datatype in a table.
    Convert the input xml to base64binary data type using java embedding. refer http://biemond.blogspot.com/2011/09/base64-encoding-in-oracle-bpel-11g.html.
    Usiong DB adapter you can then insert the base64binary data in the blob data type.
  • 5. Re: How to load XML file to DB
    1008841 Newbie
    Currently Being Moderated
    HI all,
    1)If my input is .txt file.
    we can use file adapter,using native builder,sample it,
    delimited/fixed length, we can do normal file read and file write/DB write.It works fine.
    2)If my input file is xml file,we can't use native builder format(Because it doesn't support
    delimited/fixed length/complex character in native format builder).
    so,as said in the above reply,i took a sample XML file & converted to XSD using
    File Menu > New > All Technologies tab > General > XML > XML Schema from XML Document.
    It compiles fine.
    Now,if i use file adapter(read) -- BPEL -- File adapter(write).
    Inside Transform,i am mapping filereadVariable and filewrite Variable correctly.
    If my input file is Abc.xml and its contents in read file are:
    <?xml version="1.0"?>
    <PaymentInfoMessageResponse>
    <PaymentInfoResponse>
    <TransactionType>940</TransactionType>
    <SequenceNum>04</SequenceNum>
    <CompanyCode>902</CompanyCode>
    </PaymentInfoResponse>
    <StatusCode>OK</StatusCode>
    <StatusDetail>OK</StatusDetail>
    </PaymentInfoMessageResponse>

    In write folder,my contents are

    <PaymentInfoMessageResponse>
    <imp1:PaymentInfoResponse>
    <imp1:TransactionType/>
    <imp1:SequenceNum/>
    <imp1:ReceiptTime/>
    <imp1:CompanyCode/>
    </imp1:PaymentInfoResponse>
    <imp1:StatusCode/>
    <imp1:StatusDetail/>
    </PaymentInfoMessageResponse>

    Now,if i use file adapter(read) -- BPEL -- DB adapter(merge).
    Inside Transform,i am mapping filereadVariable and DBmerge Variable correctly.
    If my input file is Abc.xml and its contents in read file are:
    <?xml version="1.0"?>
    <PaymentInfoMessageResponse>
    <PaymentInfoResponse>
    <TransactionType>940</TransactionType>
    <SequenceNum>04</SequenceNum>
    <CompanyCode>902</CompanyCode>
    </PaymentInfoResponse>
    <StatusCode>OK</StatusCode>
    <StatusDetail>OK</StatusDetail>
    </PaymentInfoMessageResponse>

    My output in em console is like this.
    <CeStatementHeadersIntCollection>
    <top:CeStatementHeadersInt>
    <top:statementNumber/>
    <top:bankName/>
    </top:CeStatementHeadersInt>
    </CeStatementHeadersIntCollection>

    NO data is being inserted into DB.

    I can write my XMl file into write folder as it is using opaque object(No native format translation)
    I can write XML file into DB using BLOB data type.
    But my requirement is:
    I have a table with columns name say TransType,SeqNum
    the value 940,04 from above XML file should be saved in TransType,SeqNum Columns respectively.
    Can i do it using DOM parser/SAX parser?
    So any suggestion/help required

    Thanks in advance
  • 6. Re: How to load XML file to DB
    991448 Journeyer
    Currently Being Moderated
    Your file adapter is configured to read the xml using schema which you created and this llooks correct because you are able to read the file contents in the flow. This looks to be more of a transformation issue.Because the output contents are not being written either in write nor to database.
    Instead of using transformation can you try mapping using assign nodes and check if the values are getting populated. Assign source node to target node individually using assign nodes.
  • 7. Re: How to load XML file to DB
    NeerajSehgal Journeyer
    Currently Being Moderated
    Hi,

    It could be some namespace error. Could you paste here the XML Schema and the file content as it looks in the EM in the Receive activity?

    Regards,
    Neeraj Sehgal
  • 8. Re: How to load XML file to DB
    user100012345 Explorer
    Currently Being Moderated
    If you are going with soa you have to follow soa convention for the schemas

    Create a schema file for the xml and then make PaymentInfoResponse as a complex type

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points