Solved

How to store and read XML file into a table

Posted on 2014-02-13
8
337 Views
Last Modified: 2014-02-24
Hi Team,
I need some help on writing some code or give me some guidence on this requirement. This is what my requirement. I have to build an XML file and store it in the table. I have created a table which has a column as CLOB. This is where I want to store this file. Below is the piece of code which generated the XML and stores it in the tempory folder

        File tmpFile = new File(longFilename);
        FileOutputStream fos = new FileOutputStream(tmpFile);
        System.out.println("File Name is " +tmpFile);
     
        MYXmlSaxBuilder myXmlSaxBuilder = new MyXmlSaxBuilder(_pos, ec);
        myXmlSaxBuilder.buildAndWriteDocument(fos);
        System.out.println("Temporary File contents are " +fos.toString());
        fos.close();

From here onwards what I need is to store this xml file into Database table along with some other columns.

My First question :- How to store this XML file into table? Please keep in mind I also need to read it later. so that I can create a single XML from different xmls
 SAMPLE
Single XML---
<?xml version="1.0" encoding="UTF-8"?>
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>

2nd question is how to read it and combine it in a single file.

COMBINED XMLs
<purchase_orders>
<transaction_set>
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>
<po_details>
  <po_hdrs>
    <company_id>1774</company_id>
    <po_number>PO166</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>
</transaction_set>
</purchase_orders>

Table
CREATE TABLE XMLTABLE
(
  PO_NUMBER     NUMBER(12)           NOT NULL,
  XMLFile                    CLOB,
  STATUS       VARCHAR2(10 CHAR)   NOT NULL,
  DESCRIPTION         VARCHAR2(256 CHAR),
  CREATION_DATE       DATE                 NOT NULL,
  EXPORTED_DATE         DATE

);


Thanks
Ali
0
Comment
Question by:AliAjoo
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39859412
Inserting rows containing LOB into a database in Java:

http://docs.oracle.com/javase/tutorial/jdbc/basics/blob.html

Creating your combined XML looks to me like simple string handling to me. You already know how to write out to a file:

Write your header out to the consolidated file:

<?xml version="1.0" encoding="UTF-8"?>
<purchase_orders>
<transaction_set>

Create a query that returns the list of XML columns you need, strip off the <?xml version="1.0" encoding="UTF-8"?> from each, and loop through the results set, writing them to a file, one after the other.

Then write out your closing tags:

</transaction_set>
</purchase_orders>
0
 

Author Comment

by:AliAjoo
ID: 39869332
Thnaks,

I am able to store XML into table, the XML is getting stored as
XML1
"(CLOB)
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>"

XML2
"(CLOB)
<po_details>
  <po_hdrs>
    <company_id>4567</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>"

Now what I need to do is trim "(CLOB), and " at the end of the xml and make one single XML

<purchase_orders>
<transaction_set>

<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>
</transaction_set>
</purchase_orders>
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39870347
Yes, that look like what you need to do.  Do you have a question?

I don't understand why would you write out ["(CLOB)] and the closing ["] to the database in the first place - it isn't part of the XML, and you just have to deal with it later.  

Suggest you post your code.

- Gary Patterson
0
 

Author Comment

by:AliAjoo
ID: 39871973
Gary,

I'm not writing CLOB and " in the database, dont undertand how it is gettting there. I'm using internal persistant ORM, it could be issue with that. How can I remove those CLOB and quotes and combine XML in one XML?

String pendingXML[] = this.getAllPendingXMLs("EXPORT_PENDING");

Thanks
Ali
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39873713
You aren't giving me much to work with.

What are the contents of pendingXML[], exactly?

Two basic choices:

1) Figure out why you have the unwanted characters showing up when you retrieve your XML using getAllPendingXMLs, and fix that.  Then it is easy:

StringBuffer finalXML = new StringBuffer();
finalXML.append("<purchase_orders><transaction_set>").append(this.getAllPendingXMLs("EXPORT_PENDING");).append("</purchase_orders></transaction_set>");

2) Assuming that pendingXML[] contains everything you need - it is just a string.  You can always just basic use string editing and extraction methods to compose the result you want.   String.replace() the components you don't want, or String.split() using a regular expression.  Better for performance to use the mutable StringBuilder than immutable String (or StringBuffer if synchronization between multiple threads is required).
0
 

Author Comment

by:AliAjoo
ID: 39880132
Thanks, I will try it like you expalined and I now figured out why those CLOB and " were in the string. To answer you questoin here is what I am trying to do.
getAllPending xml method returns me array of pending XML, I now  have to create a final xml as shown below and then copy it in some Local directory. The directory will have final XML by name XXX_DateStamp
XM1
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>

XML2
<po_details>
  <po_hdrs>
    <company_id>4567</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>

Final XML
<purchase_orders>
<transaction_set>
<po_details>
  <po_hdrs>
    <company_id>1234</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>

<po_details>
  <po_hdrs>
    <company_id>4567</company_id>
    <po_number>PO111</po_number>
    <SAP_id>134567</SAP_id>
  </po_hdrs>
</po_details>

</purchase_orders>
</transaction_set>
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 340 total points
ID: 39880830
Yes,  I understand what you want to create - and I think I've explained in quite a bit of detail how to do that.

If getAllPendingXMLs will give you the individual XML documents you need, then you just need to stick:

<purchase_orders>
<transaction_set>

On the front, and

</purchase_orders>
</transaction_set>

On the back, and write that string out to a file.  And I showed you basically how to do that in my last post.

What else do you need to know?
0
 

Author Closing Comment

by:AliAjoo
ID: 39884089
Thanks for your help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to implement Singleton Design Pattern in Java.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now