• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9425
  • Last Modified:

Write XML file to Oracle table using plsql and xmldom

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
barnarp
Asked:
barnarp
  • 6
  • 5
1 Solution
 
sdstuberCommented:
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
 
barnarpAuthor Commented:
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
 
barnarpAuthor Commented:
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
Industry Leaders: 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!

 
sdstuberCommented:
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
 
sdstuberCommented:
If you want to do an update or merge, what is the key?


0
 
barnarpAuthor Commented:
The key is request_no
0
 
sdstuberCommented:
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
 
barnarpAuthor Commented:
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
 
sdstuberCommented:
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
 
barnarpAuthor Commented:
Thank you very much. The examples helped alot.
0
 
sdstuberCommented:
glad I could help
0
 
Pradeep_RayapudiCommented:
It is Excellent Example for Beginner like me.

Thanks & Regards,
-Pradeep.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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