asked on

How to store and read XML file into a table

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

Gary Patterson, CISSP

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>

AliAjoo

ASKER

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>

Gary Patterson, CISSP

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

AliAjoo

ASKER

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

Gary Patterson, CISSP

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).

AliAjoo

ASKER

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>

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AliAjoo

ASKER

Thanks for your help