[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Write XML file to Oracle table using plsql and xmldom

Posted on 2007-12-03
12
Medium Priority
?
9,413 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
Technology Partners: 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!

 
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

650 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