[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

Loading XML File Format data into Oracle DataBase Tables Using PLSQL or UTL Procedures

I had an Urgent  requirement :
The data is the form of XML File Format  and this data to be loaded into Oracle Database Tables, Using PLSQL/UTL Procedure and performance issue should be considered
If any body can suggest with an examples PLZ
(version: oracle 9i)


0
sandeep63
Asked:
sandeep63
  • 5
  • 5
  • 3
  • +2
2 Solutions
 
sandeep63Author Commented:
XML FILE DATA--------------------> BASE TABLES
                     (USING PLSQL/UTL)
0
 
slightwv (䄆 Netminder) Commented:
I'm afraid I need a little more information.  First:  what version of 9i?  Please provide all 4 numbers.  ex./ 9.2.0.6

Second:  How are you getting the XML( From what app through what means )?

To make sure everyone is on the same page I always like working examples.  If you can provide a smalle test case that is similar to your need, I will see if I can come up with a working sample.
0
 
sandeep63Author Commented:

I am working on 9.2.0.1.0

The xml file is as follows

<?xml version = '1.0'?>
<Customers>
   <ROW>
      <CUST_ID>1</CUST_ID>
      <COMPANY_LAST_NM>lname1</COMPANY_LAST_NM>
      <UP_COMPANY_LAST_NM>LNAME1</UP_COMPANY_LAST_NM>
      <FIRST_NM>riju</FIRST_NM>
      <MIDDLE_NM>brata</MIDDLE_NM>
      <PREFIX_TX>Mr.</PREFIX_TX>
      <SUFFIX_TX>Jr.</SUFFIX_TX>
      <CLASS_TY_CD>cls1</CLASS_TY_CD>
      <PHONE_NO>9885580385</PHONE_NO>
      <UNLISTED_PHONE_NO_IN>1</UNLISTED_PHONE_NO_IN>
      <DUN_BRAD_NO>dun1</DUN_BRAD_NO>
      <CREDIT_SCORE_TX>crd1</CREDIT_SCORE_TX>
      <CREDIT_SCORE_DT>5454</CREDIT_SCORE_DT>
      <CREDIT_RISK_IND_TX>cdr</CREDIT_RISK_IND_TX>
      <CREDIT_SCORE_SOURCE_TX>2</CREDIT_SCORE_SOURCE_TX>
      <CREDIT_LIMIT_AM>6565</CREDIT_LIMIT_AM>
      <INT_CREDIT_SCORE_TX>rwrw</INT_CREDIT_SCORE_TX>
      <LOCK_ID>646</LOCK_ID>
      <SYS_TM_STAMP>1/1/2006 0:0:0</SYS_TM_STAMP>
      <SYS_USER_STAMP>rijb</SYS_USER_STAMP>
   </ROW>
   <ROW>
      <CUST_ID>2</CUST_ID>
      <COMPANY_LAST_NM>lname2</COMPANY_LAST_NM>
      <UP_COMPANY_LAST_NM>LNAME2</UP_COMPANY_LAST_NM>
      <FIRST_NM>sucha</FIRST_NM>
      <MIDDLE_NM>ndra</MIDDLE_NM>
      <PREFIX_TX>Mr.</PREFIX_TX>
      <SUFFIX_TX>Jr.</SUFFIX_TX>
      <CLASS_TY_CD>cls1</CLASS_TY_CD>
      <PHONE_NO>9885906843</PHONE_NO>
      <UNLISTED_PHONE_NO_IN>1</UNLISTED_PHONE_NO_IN>
      <DUN_BRAD_NO>dun2</DUN_BRAD_NO>
      <CREDIT_SCORE_TX>crd2</CREDIT_SCORE_TX>
      <CREDIT_SCORE_DT>5490</CREDIT_SCORE_DT>
      <CREDIT_RISK_IND_TX>cdr</CREDIT_RISK_IND_TX>
      <CREDIT_SCORE_SOURCE_TX>1</CREDIT_SCORE_SOURCE_TX>
      <CREDIT_LIMIT_AM>6565</CREDIT_LIMIT_AM>
      <INT_CREDIT_SCORE_TX>riuw</INT_CREDIT_SCORE_TX>
      <LOCK_ID>642</LOCK_ID>
      <SYS_TM_STAMP>2/1/2006 0:0:0</SYS_TM_STAMP>
      <SYS_USER_STAMP>such</SYS_USER_STAMP>
   </ROW>
</Customers>

I am reading and  storing the file in an clob object.


0
Independent Software Vendors: 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!

 
actonwangCommented:
when you load this xml data, you mean:

1.load this text content into a clob column or
2.load them into a table <ROW> by <ROW>?
0
 
actonwangCommented:
if it is the second case, you need to look at  DBMS_XMLSAVE package as in the following:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_xmlsav.htm#1008593
0
 
actonwangCommented:
you must make sure your xml file has format as:

the xml file must be in the form as:
<ROWSET>
  <ROW>
     <FIELDNAME></FIELDNAME>
       ...
  </ROW>
</ROWSET>

so you just need to change your <CUSTOMERS> tag into <ROWSET> tag.

Acton
0
 
sandeep63Author Commented:


It is the second case only..but we need to update /insert data into tables based upon the values passed. We need to read each values of xml elements and insert/update accordingly. we need to use pl/sql .
0
 
actonwangCommented:
You can set up a temp table to contain all data from xml file first using DBMS_XMLSAVE (it will just have a couple of lines of code). Starting from there, you can use "merge into .." or other query.

Complex PL/SQL codes can be avoided.

Acton
0
 
slightwv (䄆 Netminder) Commented:
First:  Get on at least 9.2.0.4 if you are going to be doing much with XML.  Oracle and XML really didn't become stable and efficient until that release.  If you can, get to 10g.  Working with XML is even quicker and in 10.2, there is a lot of new XML functionality.

If you can change the root node then I'd look into using the solution provided by Acton.  It would definatly be the easiest.

If the actual requirement is a little more complex that you've stated and since there are several ways to process XML in Oracle, I'd like a little more information.

How often will this run?
What is the average size of the XML file?
0
 
sandeep63Author Commented:
The problem got solved by using 'Extract Value '
so ..I am closing this Issue
& question is closed here
 
0
 
sandeep63Author Commented:
Thanks for suggestions provided
& i am closing this question & issue here
0
 
slightwv (䄆 Netminder) Commented:
Did I miss something here?  I thought the question was how to load the data not that the current data was loading still encoded.

I'm afraid you will need to convince me why this question should be deleted and no points awarded.  I feel that actonwang  and myself properly answered the question that was asked.
0
 
actonwangCommented:
agree with slightww. I believe that we already gave you some nice suggestions.
0
 
MohanKNairCommented:
If you know the field names then the values can be inserted to a table.
In the sample code l_xmlt holds the XML data. The tablename is passed to dbms_xmlsave.newContext. This table will have the parsed data.

Run the SQL script to create dbms_xmlsave package
$ORACLE_HOME/rdbms/admin/dbmsxml.sql

declare
l_clob CLOB;
l_ctx   dbms_xmlsave.ctxType;
l_rows  number;
l_xmlt  xmltype;
BEGIN
l_clob := xmltype.getclobval(l_xmlt);
l_ctx := dbms_xmlsave.newContext('MYTAB');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
END;
/

Also see the links
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4061080732051
http://www.oracle.com/technology/oramag/oracle/03-jul/o43xml.html


The other option is to parse the XML document using dbms_xmlparser built-in package in Oracle 9i

Use dbms_xmlparser and dbms_xmldom packages to parse the XML document and get fields withing the DOM hierarchy.

XDB has to be installed as sysdba. See the link below for installing XML DB

Installing and Configuring XML DB - http://www.idevelopment.info/data/Oracle/DBA_tips/xmldb/XMLDB_3.shtml


l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_xmlparser.freeParser(l_parser);


http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/transformxml/TransformXML.html

0
 
JimBrandleyCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup Zone:
SPLIT between actonwang(16664876) and slightwv(16667707)

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

JimBrandley
Experts Exchange Cleanup Volunteer
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now