Solved

How to store and read XML file into a table

Posted on 2014-02-13
8
355 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 35

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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 35

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 35

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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use lov values 2 71
ejb wildfly example 2 70
swing controls 2 21
Code Coverage and Coding Standards 2 43
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 …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

713 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