sandeep63
asked on
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)
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)
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.
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.
ASKER
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</C
<UP_COMPANY_LAST_NM>LNAME1
<FIRST_NM>riju</FIRST_NM>
<MIDDLE_NM>brata</MIDDLE_N
<PREFIX_TX>Mr.</PREFIX_TX>
<SUFFIX_TX>Jr.</SUFFIX_TX>
<CLASS_TY_CD>cls1</CLASS_T
<PHONE_NO>9885580385</PHON
<UNLISTED_PHONE_NO_IN>1</U
<DUN_BRAD_NO>dun1</DUN_BRA
<CREDIT_SCORE_TX>crd1</CRE
<CREDIT_SCORE_DT>5454</CRE
<CREDIT_RISK_IND_TX>cdr</C
<CREDIT_SCORE_SOURCE_TX>2<
<CREDIT_LIMIT_AM>6565</CRE
<INT_CREDIT_SCORE_TX>rwrw<
<LOCK_ID>646</LOCK_ID>
<SYS_TM_STAMP>1/1/2006 0:0:0</SYS_TM_STAMP>
<SYS_USER_STAMP>rijb</SYS_
</ROW>
<ROW>
<CUST_ID>2</CUST_ID>
<COMPANY_LAST_NM>lname2</C
<UP_COMPANY_LAST_NM>LNAME2
<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_T
<PHONE_NO>9885906843</PHON
<UNLISTED_PHONE_NO_IN>1</U
<DUN_BRAD_NO>dun2</DUN_BRA
<CREDIT_SCORE_TX>crd2</CRE
<CREDIT_SCORE_DT>5490</CRE
<CREDIT_RISK_IND_TX>cdr</C
<CREDIT_SCORE_SOURCE_TX>1<
<CREDIT_LIMIT_AM>6565</CRE
<INT_CREDIT_SCORE_TX>riuw<
<LOCK_ID>642</LOCK_ID>
<SYS_TM_STAMP>2/1/2006 0:0:0</SYS_TM_STAMP>
<SYS_USER_STAMP>such</SYS_
</ROW>
</Customers>
I am reading and storing the file in an clob object.
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>?
1.load this text content into a clob column or
2.load them into a table <ROW> by <ROW>?
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
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_xmlsav.htm#1008593
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
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
ASKER
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 .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The problem got solved by using 'Extract Value '
so ..I am closing this Issue
& question is closed here
so ..I am closing this Issue
& question is closed here
ASKER
Thanks for suggestions provided
& i am closing this question & issue here
& i am closing this question & issue here
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.
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.
agree with slightww. I believe that we already gave you some nice suggestions.
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/d bmsxml.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('M YTAB');
l_rows := dbms_xmlsave.insertxml(l_c tx,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
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/d
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('M
l_rows := dbms_xmlsave.insertxml(l_c
dbms_xmlsave.closeContext(
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
l_doc := dbms_xmlparser.getDocument
dbms_xmlparser.freeParser(
http://www.oracle-base.com/articles/9i/ParseXMLDocuments9i.php
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/transformxml/TransformXML.html
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
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
ASKER
(USING PLSQL/UTL)