Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Ajoo Bar
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 236
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can see this on storing CLOBs in database.
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13061
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic