?
Solved

Write XML file to Oracle table using plsql and xmldom

Posted on 2007-12-03
12
Medium Priority
?
9,392 Views
Last Modified: 2013-12-07
Hi,

I need to open and read a XML file on the server, parse it, and update a relational table on Oracle 10g using pl/sql (XMLDom).

An example of the xml file (test.xml):

<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  

The table would look something like this:

CREATE TABLE IT02M_VIS_REQUEST
(
  REQUEST_NO                      NUMBER(13)    NOT NULL,
  SERVICE_SUPPLIER                VARCHAR2(100 BYTE) NOT NULL,
  REQUEST_DATE                    DATE          NOT NULL
)  

Can someone please give me some example code for the open/read and parse of the xml file.

I have created a package to create an xml file from the table, but I am struggling with the update back to the table from the file.

Regards
0
Comment
Question by:barnarp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20398791
SELECT TO_NUMBER(EXTRACTVALUE(VALUE(x), '/RECORD/REQUEST_NUMBER')) request_number,
       TO_DATE(EXTRACTVALUE(VALUE(x), '/RECORD/DATE_CREATED'), 'dd-MON-rr') date_created,
       EXTRACTVALUE(VALUE(x), '/RECORD/COMPANY_CODE') company_code
  FROM (SELECT XMLTYPE
                   ('<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  
'
                   ) xml
          FROM DUAL),
       TABLE(XMLSEQUENCE(EXTRACT(xml, '/NIDREQUEST/RECORD'))) x


This query will show you how to extract the data you want
and from here you can do an insert or merge or iterate through the rows as a cursor and process each of them before doing an insert or update

0
 

Author Comment

by:barnarp
ID: 20401673
Thanks, but how do I get the XML into a variable I can work with?

Should it be assigned to a CLOB?

I need to know how to read and open the file and assign it to a CLOB (if that's the way to go).

Please advise.
0
 

Author Comment

by:barnarp
ID: 20401704
Also, how do I loop through the records in the XML code to process each one in turn?

I have <RECORD num="1">,<RECORD num="2"> in the XML so that should be useful.

Regards
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Expert Comment

by:sdstuber
ID: 20402679
You can use that query in a cursor and loop through the data like you would any other cursor.  


BEGIN
    FOR r IN
        (SELECT TO_NUMBER(EXTRACTVALUE(VALUE(x), '/RECORD/REQUEST_NUMBER')) request_number,
                TO_DATE(EXTRACTVALUE(VALUE(x), '/RECORD/DATE_CREATED'), 'dd-MON-rr') date_created,
                EXTRACTVALUE(VALUE(x), '/RECORD/COMPANY_CODE') company_code
           FROM (SELECT XMLTYPE
                            ('<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  
'
                            ) xml
                   FROM DUAL),
                TABLE(XMLSEQUENCE(EXTRACT(xml, '/NIDREQUEST/RECORD'))) x)
    LOOP
        DBMS_OUTPUT.put_line(   r.request_number
                             || '   '
                             || TO_CHAR(r.date_created, 'yyyy-mm-dd')
                             || '   '
                             || r.company_code
                            );
    END LOOP;
END;


You don't need a clob at all.

Unless you need to do something to each record, then don't do the loop at all,  simply insert the data all at once.

INSERT INTO it02m_vis_request
            (request_no, request_date, service_supplier)
    SELECT TO_NUMBER(EXTRACTVALUE(VALUE(x), '/RECORD/REQUEST_NUMBER')) request_number,
           TO_DATE(EXTRACTVALUE(VALUE(x), '/RECORD/DATE_CREATED'), 'dd-MON-rr') date_created,
           EXTRACTVALUE(VALUE(x), '/RECORD/COMPANY_CODE') company_code
      FROM (SELECT XMLTYPE
                       ('<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  
'
                       ) xml
              FROM DUAL),
           TABLE(XMLSEQUENCE(EXTRACT(xml, '/NIDREQUEST/RECORD'))) x



0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20402685
If you want to do an update or merge, what is the key?


0
 

Author Comment

by:barnarp
ID: 20409693
The key is request_no
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20411239
MERGE INTO it02m_vis_request i
    USING (SELECT TO_NUMBER(EXTRACTVALUE(VALUE(x), '/RECORD/REQUEST_NUMBER')) request_number,
                  TO_DATE(EXTRACTVALUE(VALUE(x), '/RECORD/DATE_CREATED'), 'dd-MON-rr') date_created,
                  EXTRACTVALUE(VALUE(x), '/RECORD/COMPANY_CODE') company_code
             FROM (SELECT XMLTYPE
                              ('<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  
'
                              ) xml
                     FROM DUAL),
                  TABLE(XMLSEQUENCE(EXTRACT(xml, '/NIDREQUEST/RECORD'))) x) r
    ON (i.request_no = r.request_number)
    WHEN MATCHED THEN
        UPDATE
           SET i.request_date = r.date_created, i.service_supplier = r.company_code
    WHEN NOT MATCHED THEN
        INSERT(request_no, request_date, service_supplier)
        VALUES(r.request_number, r.date_created, r.company_code)
0
 

Author Comment

by:barnarp
ID: 20417643
Thanks for the help sofar, just one thing I dont understand.

The xml file (ie test.xml) still sits on the server. How do I "read" in the file?

Say the xml file has 1000 records, instead of the hardcoded code:
'<?xml version="1.0" encoding="UTF-8"?>
<NIDREQUEST>
  <RECORD num="1">
    <REQUEST_NUMBER>100</REQUEST_NUMBER>
    <DATE_CREATED>23-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MF</COMPANY_CODE>
  </RECORD>
  <RECORD num="2">
    <REQUEST_NUMBER>200</REQUEST_NUMBER>
    <DATE_CREATED>24-NOV-07</DATE_CREATED>
    <COMPANY_CODE>MS</COMPANY_CODE>
  </RECORD>
</NIDREQUEST>  
'
I must replace it with a variable like using FILE_UTL package and then read into a CLOB or something?!
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 20419347
Yes, you can use utl_file to read the text into a clob.

You can also reference the file as a bfile. Open it and read directly into a clob, then it's the same statement as before, except you use the clob variable instead of the hardcoded text.


DECLARE
    v_bfile   BFILE := BFILENAME('DTEMP', 'test.xml');
    v_clob    CLOB;
BEGIN
    DBMS_LOB.createtemporary(v_clob, TRUE);
    DBMS_LOB.OPEN(v_bfile, DBMS_LOB.lob_readonly);
    DBMS_LOB.loadfromfile(v_clob, v_bfile, DBMS_LOB.lobmaxsize);
    DBMS_OUTPUT.put_line(v_clob);
    MERGE INTO it02m_vis_request i
        USING (SELECT TO_NUMBER(EXTRACTVALUE(VALUE(x), '/RECORD/REQUEST_NUMBER')) request_number,
                      TO_DATE(EXTRACTVALUE(VALUE(x), '/RECORD/DATE_CREATED'), 'dd-MON-rr') date_created,
                      EXTRACTVALUE(VALUE(x), '/RECORD/COMPANY_CODE') company_code
                 FROM (SELECT XMLTYPE(v_clob) xml
                         FROM DUAL),
                      TABLE(XMLSEQUENCE(EXTRACT(xml, '/NIDREQUEST/RECORD'))) x) r
        ON (i.request_no = r.request_number)
        WHEN MATCHED THEN
            UPDATE
               SET i.request_date = r.date_created, i.service_supplier = r.company_code
        WHEN NOT MATCHED THEN
            INSERT(request_no, request_date, service_supplier)
            VALUES(r.request_number, r.date_created, r.company_code);
    COMMIT;
END;
0
 

Author Comment

by:barnarp
ID: 20420222
Thank you very much. The examples helped alot.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20420388
glad I could help
0
 

Expert Comment

by:Pradeep_Rayapudi
ID: 23849433
It is Excellent Example for Beginner like me.

Thanks & Regards,
-Pradeep.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

764 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