Home » Developer & Programmer » JDeveloper, Java & XML » loading XML file (oracle 10g )
loading XML file [message #394516] Fri, 27 March 2009 10:05
kanchan_pal
Messages: 1
Registered: March 2009
Junior Member
HI i am loading below xml files in oracle 10g , but its inserting blank records , values are not populating .

is there any other way i can do that or tell me where i am doing wrong .

content of my xml file
----------------------
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="jid">1169</field>
<field name="pqjid"></field>
<field name="country">Puerto Rico</field>
<field name="title">Acceso</field>
<field name="city">San Juan</field>
<field name="peer">N</field>
</row>
<row>
<field name="jid">13</field>
<field name="pqjid">36084</field>
<field name="country">USA</field>
<field name="title">Afro-Hispanic Review</field>
<field name="city">Nashville, Tenn.</field>
<field name="peer">Y</field>
</row>
</ROOT>
---------------------
code-
DECLARE
l_clob CLOB;
l_bfile BFILE;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);


TYPE tab_type IS TABLE OF stage_xml%ROWTYPE;
t_tab tab_type := tab_type();
BEGIN

l_bfile := BFileName('XML_DIR', 'prisma_journals.xml');

dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
dbms_lob.close(l_bfile);




-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

-- Create a parser.
l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);

l_doc := dbms_xmlparser.getDocument(l_parser);

-- l_doc := dbms_xmldom.newDomDocument(l_clob);


-- Free resources associated with the CLOB and Parser now they are no longer needed.

dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);

-- Get a list of all the row nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'ROOT/row');


-- Loop through the list and create a new record in a tble collection
-- for each stage_xml_data record.
FOR cur_stage_xml IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP

l_n := dbms_xmldom.item(l_nl, cur_stage_xml);

t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.

dbms_xslprocessor.valueOf(l_n,'jid/text()',t_tab(t_tab.last).fjid);
dbms_xslprocessor.valueOf(l_n,'pqjid/text()',t_tab(t_tab.last).fpqjid);
dbms_xslprocessor.valueOf(l_n,'country/text()',t_tab(t_tab.last).fcountry);
dbms_xslprocessor.valueOf(l_n,'title/text()',t_tab(t_tab.last).ftitle);
dbms_xslprocessor.valueOf(l_n,'city/text()',t_tab(t_tab.last).fcity);
dbms_xslprocessor.valueOf(l_n,'peer/text()',t_tab(t_tab.last).fpeer);

END LOOP;

-- Insert data into the real stage_xml table from the table collection.

FOR cur_stage_xml IN t_tab.first .. t_tab.last LOOP
dbms_output.put_line ('1--' || t_tab(cur_stage_xml).fjid||'***');
INSERT INTO
stage_xml (
fJid ,
fPqjid ,
fCountry ,
fTitle ,
fCity ,
fPeer
)

VALUES
(t_tab(cur_stage_xml).fjid,
t_tab(cur_stage_xml).fpqjid ,
t_tab(cur_stage_xml).fcountry,
t_tab(cur_stage_xml).ftitle,
t_tab(cur_stage_xml).fcity,
t_tab(cur_stage_xml).fpeer

);
END LOOP;

COMMIT;

-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
raise ;
END;
----------------------
table
Create table stage_xml (
fJid varchar2(10) ,
fPqjid varchar2(100) ,
fCountry varchar2(100) ,
fTitle varchar2(100) ,
fCity varchar2(100) ,
fPeer varchar2(100) )
/
Previous Topic: XML parsing in PL/SQL - namespace aware off
Next Topic: JDeveloper and BLOB
Goto Forum:
  


Current Time: Thu Mar 28 07:57:32 CDT 2024