Solved

How to store and read XML file into a table

Posted on 2014-02-13
8
350 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

791 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