[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

problem loading xml data into oracle

Hi experts,
I am trying to load an xml file into an Oracle database table. I have written a stored procedure to perform the load, it compiles successfully, but nothing gets loaded. I am having problem with the tree structure. I don't know how to direct oracle to the location of the data. Below is a sample of the xml file and my store procedure.


<Nuisance>
<Jobs>
<Job Status="STOPPED" JobNumber="695" JobName="w0_xm4"
StartTime="09:12:15" />
<Job Status="STOPPED" JobNumber="696" JobName="w0_xm2"
StartTime="15:17:33"/>
</Jobs>
</Nuisance>

l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/Nuisance/Jobs');

  -- Loop through the list and create a new record in a tble collection
  -- for each Nuis record.
  FOR cur_nuis IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_nuis);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'job.Status/text()',t_tab(t_tab.last).Status);
    dbms_xslprocessor.valueOf(l_n,'JobNumber/text()',t_tab(t_tab.last).JobNumber);
    dbms_xslprocessor.valueOf(l_n,'JobName/text()',t_tab(t_tab.last).JobName);
    dbms_xslprocessor.valueOf(l_n,'StartTime/text()',t_tab(t_tab.last).StartTime);
0
jkopoku
Asked:
jkopoku
  • 4
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
Since you don't provide all the code I can only guess why you are loading a PL/SQL table but see if this example will help.

I've also provided a direct way to do the insert that might work for you as well.
------------------
drop table tab1;
create table tab1(
      jobNumber varchar2(50),
      startTime      varchar2(50)
)
/

create or replace procedure junk(inXml in clob)
is
      doc                  dbms_xmldom.DOMDocument;
      Node            dbms_xmldom.DOMNode;
      Node1            dbms_xmldom.DOMNode;
      NodeList      dbms_xmldom.DOMNodeList;

      num_nodes      number;
      i                  number;

      jobNumber varchar2(50);
      startTime varchar2(50);
begin
      doc := dbms_xmldom.newDomDocument(inXml);
      NodeList := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(doc),'/Nuisance/Jobs/*');
      num_nodes := dbms_xmldom.getLength(NodeList);

      for i in 0..num_nodes-1 loop
            node1 := dbms_xmldom.item(nodeList, i);
            jobNumber := dbms_xslprocessor.valueOf(node1,'./@JobNumber');
            startTime := dbms_xslprocessor.valueOf(node1,'./@StartTime');

            insert into tab1 values( jobNumber, startTime );

      end loop;
end;
/

show errors


exec junk(' -
<Nuisance> -
<Jobs> -
<Job Status="STOPPED" JobNumber="695" JobName="w0_xm4" StartTime="09:12:15" /> -
<Job Status="STOPPED" JobNumber="696" JobName="w0_xm2" StartTime="15:17:33"/> -
</Jobs> -
</Nuisance> -
' -
);

commit;

select * from tab1;

delete from tab1;
commit;


insert into tab1 (
      select extractvalue(column_value,'//@JobNumber'), extractvalue(column_value,'//@StartTime')
      from table(xmlsequence(extract(xmltype(
      '
            <Nuisance>
            <Jobs>
            <Job Status="STOPPED" JobNumber="695" JobName="w0_xm4" StartTime="09:12:15" />
            <Job Status="STOPPED" JobNumber="696" JobName="w0_xm2" StartTime="15:17:33"/>
            </Jobs>
            </Nuisance>
      '
      )
      , '/Nuisance/Jobs/*')))
)
/

commit;

select * from tab1;

0
 
jkopokuAuthor Commented:
Here is sample of the xml file and all the code. My only problem is that, the second Job node is not inserting, only the first one does.


<Nuisance>
<DialerName>wau1pds1</DialerName>
<Date>2006/03/01</Date>
<LastUpdate>00:01:14</LastUpdate>
<WaitTime>-1</WaitTime>
<ClientRefresh>30</ClientRefresh>
<Jobs>
<Job Status="STOPPED" JobNumber="695" JobName="w0_xm4"
StartTime="09:12:15" StopTime="15:53:46" CallsOffered="877"
NuisanceCount="8" NROffered="0.0091" CurrentNROffered="0.0100"
CustomerHangup="5" CurrentCalls="100" CurrentCount="1"/>
<Job Status="STOPPED" JobNumber="696" JobName="w0_xm2"
StartTime="15:17:33" StopTime="20:42:10" CallsOffered="1118"
NuisanceCount="21" NROffered="0.0188" CurrentNROffered="0.0300"
CustomerHangup="4" CurrentCalls="100" CurrentCount="3"/>
</Jobs>
</Nuisance>


DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  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 job_nuis2%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  l_bfile := BFileName('XML_DIR', 'nuis3_1.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);

  -- 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 nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'Nuisance');

  -- Loop through the list and create a new record in a tble collection
  -- for each record.
  FOR cur_nuis in 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_nuis);

    t_tab.extend;

    -- Use XPATH syntax to assign values to the elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'DialerName/text()',t_tab(t_tab.last).DialerName);
    dbms_xslprocessor.valueOf(l_n,'Jobdate/text()',t_tab(t_tab.last).Jobdate);
    dbms_xslprocessor.valueOf(l_n,'LastUpdate/text()',t_tab(t_tab.last).LastUpdate);
    dbms_xslprocessor.valueOf(l_n,'WaitTime/text()',t_tab(t_tab.last).WaitTime);
    dbms_xslprocessor.valueOf(l_n,'ClientRefresh/text()',t_tab(t_tab.last).ClientRefresh);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job/@Status',t_tab(t_tab.last).Status);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job/@JobNumber',t_tab(t_tab.last).JobNumber);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@JobName',t_tab(t_tab.last).JobName);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@StartTime',t_tab(t_tab.last).StartTime);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@StopTime',t_tab(t_tab.last).StopTime);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@CallsOffered',t_tab(t_tab.last).CallsOffered);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@NuisanceCount',t_tab(t_tab.last).NuisanceCount);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@NROffered',t_tab(t_tab.last).NROffered);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@CurrentNROffered',t_tab(t_tab.last).CurrentNROffered);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@CustomerHangup',t_tab(t_tab.last).CustomerHangup);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@CurrentCalls',t_tab(t_tab.last).CurrentCalls);
    dbms_xslprocessor.valueOf(l_n,'Jobs/Job@CurrentCount',t_tab(t_tab.last).CurrentCount);
     
  END LOOP;

  FOR cur_nuis IN t_tab.first .. t_tab.last LOOP
    INSERT INTO job_nuis2
    (DialerName,
Jobdate,
LastUpdate,
WaitTime,
ClientRefresh,
Status,
JobNumber,
JobName,
StartTime,
StopTime,
CallsOffered,
NuisanceCount,
NROffered,
CurrentNROffered,
CustomerHangup,
CurrentCalls,
CurrentCount )
    VALUES
    (t_tab(cur_nuis).DialerName,
     t_tab(cur_nuis).Jobdate,
     t_tab(cur_nuis).LastUpdate,
     t_tab(cur_nuis).WaitTime,
     t_tab(cur_nuis).ClientRefresh,
     t_tab(cur_nuis).Status,
     t_tab(cur_nuis).JobNumber,
     t_tab(cur_nuis).JobName,
     t_tab(cur_nuis).StartTime,
     t_tab(cur_nuis).StopTime,
     t_tab(cur_nuis).CallsOffered,
     t_tab(cur_nuis).NuisanceCount,
     t_tab(cur_nuis).NROffered,
     t_tab(cur_nuis).CurrentNROffered,
     t_tab(cur_nuis).CustomerHangup,
     t_tab(cur_nuis).CurrentCalls,
     t_tab(cur_nuis).CurrentCount);
  END LOOP;
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!

 
slightwv (䄆 Netminder) Commented:
The problem is with:
 l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'Nuisance');

There is only 1 node under 'Nuisance' hence only 1 iteration.  You'll need to get the top values (DialerName, Date, etc...) first then loop through the jobs.

I think what you posted can be cleaned up a bit.

Take a look at this:
------------------------------------------------------
drop directory xml_dir;
create directory xml_dir as 'c:\';

drop table tab1;
create table tab1(
      DialerName varchar2(20),
      Jobdate varchar2(20),
      LastUpdate varchar2(20),
      WaitTime varchar2(20),
      ClientRefresh varchar2(20),
      Status varchar2(20),
      JobNumber varchar2(20),
      JobName varchar2(20),
      StartTime varchar2(20),
      StopTime varchar2(20),
      CallsOffered varchar2(20),
      NuisanceCount varchar2(20),
      NROffered varchar2(20),
      CurrentNROffered varchar2(20),
      CustomerHangup varchar2(20),
      CurrentCalls varchar2(20),
      CurrentCount varchar2(20)
)
/

create or replace procedure junk
is

       l_bfile bfile;
       l_clob      clob;

       t_row tab1%ROWTYPE;

       doc            dbms_xmldom.DOMDocument;
       node             dbms_xmldom.DOMNode;
       NodeList      dbms_xmldom.DOMNodeList;

       num_nodes      number;
       i                  number;
begin

      l_bfile := BFileName('XML_DIR', 'q.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);


      doc := dbms_xmldom.newDomDocument(l_clob);
      dbms_lob.freetemporary(l_clob);


      node := xmldom.makeNode(doc);
      node := dbms_xslprocessor.selectSingleNode(node,'//Nuisance');

      dbms_xslprocessor.valueOf(node,'DialerName/text()',t_row.DialerName);
      dbms_xslprocessor.valueOf(node,'LastUpdate/text()',t_row.LastUpdate);
      dbms_xslprocessor.valueOf(node,'WaitTime/text()',t_row.WaitTime);
      dbms_xslprocessor.valueOf(node,'ClientRefresh/text()',t_row.ClientRefresh);


       NodeList := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(doc),'//Nuisance/Jobs/*');
       num_nodes := dbms_xmldom.getLength(NodeList);

       for i in 0..num_nodes-1 loop
            node := dbms_xmldom.item(nodeList, i);

            dbms_xslprocessor.valueOf(node,'./@Status',t_row.Status);
            dbms_xslprocessor.valueOf(node,'./@JobNumber',t_row.JobNumber);
            dbms_xslprocessor.valueOf(node,'./@JobName',t_row.JobName);
            dbms_xslprocessor.valueOf(node,'./@StartTime',t_row.StartTime);
            dbms_xslprocessor.valueOf(node,'./@StopTime',t_row.StopTime);
            dbms_xslprocessor.valueOf(node,'./@CallsOffered',t_row.CallsOffered);
            dbms_xslprocessor.valueOf(node,'./@NuisanceCount',t_row.NuisanceCount);
            dbms_xslprocessor.valueOf(node,'./@NROffered',t_row.NROffered);
            dbms_xslprocessor.valueOf(node,'./@CurrentNROffered',t_row.CurrentNROffered);
            dbms_xslprocessor.valueOf(node,'./@CustomerHangup',t_row.CustomerHangup);
            dbms_xslprocessor.valueOf(node,'./@CurrentCalls',t_row.CurrentCalls);
            dbms_xslprocessor.valueOf(node,'./@CurrentCount',t_row.CurrentCount);

            insert into tab1 values t_row;

       end loop;
end;
/

show errors


exec junk;
commit;

select * from tab1;


0
 
jkopokuAuthor Commented:
slightwv,
I am not familiar with xmldom.makeNode, how do I declare it.
0
 
slightwv (䄆 Netminder) Commented:
Sorry about that.  All the old XML stuff didn't have 'dbms_' in front of it.  I tend to mix and match across all my samples that I cut and paste from.  Depending on your version, both may still exist.

Just add the 'dbms_' in front of it and it should work.
0
 
jkopokuAuthor Commented:
It inserts just as I want, but I don't konw why the output looks like this. It produces multiple column names. Can you please help me with that?

SQL> select * from tab1;


DIALERNAME           JOBDATE              LASTUPDATE
-------------------- -------------------- --------------------
WAITTIME             CLIENTREFRESH        STATUS
-------------------- -------------------- --------------------
JOBNUMBER            JOBNAME              STARTTIME
-------------------- -------------------- --------------------
STOPTIME             CALLSOFFERED         NUISANCECOUNT
-------------------- -------------------- --------------------
NROFFERED            CURRENTNROFFERED     CUSTOMERHANGUP
-------------------- -------------------- --------------------
CURRENTCALLS         CURRENTCOUNT
-------------------- --------------------
wau1pds1                                  00:01:14


DIALERNAME           JOBDATE              LASTUPDATE
-------------------- -------------------- --------------------
WAITTIME             CLIENTREFRESH        STATUS
-------------------- -------------------- --------------------
JOBNUMBER            JOBNAME              STARTTIME
-------------------- -------------------- --------------------
STOPTIME             CALLSOFFERED         NUISANCECOUNT
-------------------- -------------------- --------------------
NROFFERED            CURRENTNROFFERED     CUSTOMERHANGUP
-------------------- -------------------- --------------------
CURRENTCALLS         CURRENTCOUNT
-------------------- --------------------
-1                   30                   STOPPED


DIALERNAME           JOBDATE              LASTUPDATE
-------------------- -------------------- --------------------
WAITTIME             CLIENTREFRESH        STATUS
-------------------- -------------------- --------------------
JOBNUMBER            JOBNAME              STARTTIME
-------------------- -------------------- --------------------
STOPTIME             CALLSOFFERED         NUISANCECOUNT
-------------------- -------------------- --------------------
NROFFERED            CURRENTNROFFERED     CUSTOMERHANGUP
-------------------- -------------------- --------------------
CURRENTCALLS         CURRENTCOUNT
-------------------- --------------------
0
 
slightwv (䄆 Netminder) Commented:
SQL*Plus wraps based on the sql*plus variable linesize.  It breaks on the variable pagesize.  The downside is that it prints every column name in the result set for each part of the row it displays.

I'm guessing that you want everything on a single line.

To get a list of all SQL*Plus variables do a 'show all' from the sql prompt.

Try this from the SQL prompt (sqlplus varialbe only need the first 5 characters of the name):
set lines 32767
set trims on

Note:
trimspool truncates ending whitespace at the end of the line.  Otherwise you would end up with 32767 characters in any spooled output.
also, seting pages to 0 stops the column headings from printing:  set pages 0
0
 
jkopokuAuthor Commented:
Slightwv,
Thanks a lot. It is working perfectly.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now