Solved

Write XML file to Oracle table using plsql and xmldom

Posted on 2007-12-03
12
9,348 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
  • 6
  • 5
12 Comments
 
LVL 73

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
 
LVL 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

Accepted Solution

by:
sdstuber earned 250 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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now