File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes How to store XML file into Database and read it back? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "How to store XML file into Database and read it back?" Watch "How to store XML file into Database and read it back?" New topic
Author

How to store XML file into Database and read it back?

Ajoo Bar
Ranch Hand

Joined: Mar 28, 2011
Posts: 30
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>
<po_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>
</po_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,


);


Thanks
Ali
Surendra Kumar
Ranch Hand

Joined: Jul 04, 2006
Posts: 225
You can see this on storing CLOBs in database.
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12675
    
    5
If CLOB bulk is a problem, note that XML documents respond well to zip compression due to the repeated tag names and other character sequences.

Bill

Java Resources at www.wbrogden.com
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39543
    
  27
If CLOB bulk is a problem, note that XML documents respond well to zip compression
...keeping in mind that ZIP is binary data, so you'd need a BLOB field in that case.


Ping & DNS - updated with new look and Ping home screen widget
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to store XML file into Database and read it back?
 
Similar Threads
retrieve CLOB data and display on browser
Mapping problem - two tables, one entity
iBatis LONG VARCHAR column mapping
How to read filed values from a clob of xml type
Reading a text file