?
Solved

How to store and read XML file into a table

Posted on 2014-02-13
8
Medium Priority
?
392 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 36

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 36

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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
 
LVL 36

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 36

Accepted Solution

by:
Gary Patterson earned 1360 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
A solution for Fortify Path Manipulation.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

862 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