Friday, September 25, 2020
dbametrix
More
    Home Oracle Troubleshooting XML Sheet to Oracle Database

    XML Sheet to Oracle Database

    Blog post guides how to transfer Data from Simple XML Sheet to Oracle Database. Using this trick you can get result.

    How to transfer Data from Simple XML Sheet to Oracle Database?

    It is the actual job of ETL Developer which will fall into general category here. In general we all use some tools to do this job of transferring data from XML to Oracle DB. In-fact Oracle itself has an tool, which will do the job with minimal steps.

    XSU is the name of the utility which can be expanded as XML to SQL Utility. There are in general two approaches to do this.

    - Advertisement -
    dbametrix

    1. PL/SQL
    2. Java

    I tried out the PL/SQL and succeeded. I am a dummy in Java, so I cannot help you out in that.

    First of all make a note of the per-requisites.

    This is the XML file I am considering.
    <?xml version=”1.0″ encoding=”utf-8″ ?>
    – <ROWSET>
    S <ROW num = “1”>
    <EMPLOYEE_ID> 7370 </EMPLOYEE_ID>
    <FIRST_NAME>ORACLE</FIRST_NAME>
    <LAST_NAME>VIRUS</LAST_NAME>
    <SALARY>100000000</SALARY>
    <PHONE_NO>555555</PHONE_NO>
    <DEPT>BUSINESS_INTELLIGENCE</DEPT>
    </ROW>
    </ROWSET>

    - Advertisement -
    dbametrix

    Now I will create a table in database with CLOB column.

    CREATE TABLE xmldocument
    (docid NUMBER PRIMARY KEY, xml_text CLOB);

    Now dump this xml document into that table.

    INSERT INTO XMLDOCUMENT VALUES
    ('1'. '<?xml version="1.0" encoding="utf-8" ?>
    <ROWSET>
    <ROW num = "1">
    <EMPLOYEE_ID> 7370 </EMPLOYEE_ID>
    <FIRST_NAME>ORACLE</FIRST_NAME>
    <LAST_NAME>VIRUS</LAST_NAME>
    <SALARY>100000000</SALARY>
    <PHONE_NO>111111</PHONE_NO>
    <DEPT>BUSINESS_INTELLIGENCE</DEPT>
    </ROW>
    </ROWSET>');

    Now the xml document is in database which u just have to parse it into proper columns in a proper table.

    Before that we have to create a stored procedure in the database as i did below:

    CREATE OR REPLACE PROCEDURE proc_inc (xml_doc IN CLOB, table_name IN VARCHAR2)
    IS
    insctx dbms_xmlsave.ctxtype;
    ROWS NUMBER;
    BEGIN
    ins_ctx := dbms_xmlsave.newcontext (table_name); -- get the context handle
    ROWS := dbms_xmlsave.insertxml (insctx, xml_doc); -- this inserts the document
    dbms_xmlsave.closecontext (ins_ctx); -- this closes the handle
    END;
    /

    Now BEFORE passing these values, you must have an Employees table with the same structure. If any column is missing in XML doc, then it will insert NULL.

    CREATE OR REPLACE TABLE EMPLOYEES(EMPLOYEE_ID NUMBER,
    FIRST_NAME VARCHAR2(30),
    LAST_NAME VARCHAR2(30),
    PHONE_NO VARCHAR2(30),
    SALARY NUMBER,
    DEPT VARCHAR2(30));

    Now the final step…proceed towards inserting data into oracle table.

    DECLARE
    v_xml_text CLOB;
    BEGIN
    SELECT xml_text
    INTO v_xml_text
    FROM XMLDOCUMENT
    WHERE DOCID = 1;
    INSPROC(V_XML_TEXT, 'employees');
    END;
    /

    Now you can check the data in oracle table by querying it.

    SELECT * FROM EMPLOYEES;

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    ORA-01194: file 1 needs more recovery to be consistent

    The blog post explains how to restore and recover database using until cancel with error ORA-01194

    How to enable Archivelog

    This blog post explains how to enable archive log mode in Oracle database for newest versions.

    Shared vs Static Library Performance

    The article explains the benefits of a shared library and static library usage in application building for improving application performance.

    Migration methods of Oracle Database

    Blog post explains which tricks and methods are simple to perform migration of small and large Oracle database

    Recent Comments