How to save XML file in PL/SQL database

Question

I need to store the text of an XML file that I already have stored in a location of an FTP, in a field of a table in the database, I am using the package UTL_FILE to read files, I am not very clear about the operation of the package and I just start with the topic of procedures in PL/SQL. My approach to date is to:

1. Use UTL_FILE.FOPEN to open the file.
2. Create an INSERT inside procedure to store in a database field

For this I am performing a procedure in PL/SQL I do not know if it is the right thing for what I am requiring

    DECLARE

    file BFILE;
    clob CLOB;
    FILE UTL_FILE.file_type;
    TEXT VARCHAR2(32767);
    L_CONN UTL_TCP.connection;
    CODE SPRCMNT.MNTCODE%TYPE;
    TEXT      MNT.MNT_TEXT%TYPE;
    TEXT_NAR  MNT.MNT_TEXT_NAR%TYPE;

BEGIN

    L_CONN := FTP.LOGIN('000.00.000.00', '00', '*********', '*********');
    FTP.ASCII(P_CONN => L_CONN);
    FTP.GET(P_CONN => L_CONN, p_from_file => '/xml_file/file.xml', p_to_dir => 'UPLOAD_DIR',  p_to_file => 'file.xml');
    ftp.logout(l_conn);

    VFILE := UTL_FILE.fopen('UPLOAD_DIR', 'file.xml', 'R', 4000);

    LOOP
        BEGIN
            UTL_FILE.GET_LINE(VFILE, TEXT, 32767);
            DBMS_OUTPUT.PUT_LINE(TEXT);
            INSERT INTO SPRETA (
                     ID,
                     CODE,
                     TEXT,
                     TEXT_NAR,
                     DATE)
                            VALUES('15218',
                                   '15',
                                   TEXT,
                                   TEXT_NAR,
                                   SYSDATE)
                                   RETURN TEXT_NAR INTO l_clob;

         l_bfile := BFILENAME ('UPLOAD', 'file.xml');
         dbms_lob.fileopen( l_bfile, dbms_lob.FILE_READONLY );
         dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength(l_bfile) );
         dbms_lob.fileclose( l_bfile );
         COMMIT;

         EXCEPTION 
            WHEN OTHERS THEN EXIT;
                   dbms_output.put_line('Error al cargar el archivo');  

        END;
    END LOOP;

    UTL_FILE.FCLOSE(FILE);

END;

Where it is CMTT_CODE going to save by default the value 15, TEXT save the name of the XML file and TEXT_NAR all the content, I mean all the XML text in the table MNT

I still can't insert all the XML text in TEXT_NAR the table field MNT.

I am trying to adhere to my keep it simple philosophy but I am new to all this and would really appreciate some feedback on the best way to do it.

The main error is that it does not save the XML name and what the XML file contains in its corresponding fields

Answer 1

Whilst it's true that the cleanest way to do this is to have the XML data file sent to the database server and use UTL_FILE to load and process, with the right tools you can go some way to bridging that client/server gap.

The method outlined below relies on client scripting to constructing a SQL*Plus script that initialises a CLOB variable with the XML data in a anonymous block, which then facilitates the use of that variable is SQL.

The example below is in bash - if you have bash or Cygwin install on your client (WIndows), or your client is Linux/MacOS, this might help you. If not, maybe someone could write a DOS BAT/Powershell equivalent.

The idea is to construct a script with the XML data presented as a variable in an anonymous PLSQL block and then pass onto a stored procedure.

Here is the bash script the_script.sh:

XML_FILE=${1}   # File containing XML to load

SQL_SCRIPT=the_script.sql   # script we will construct to do the work

# Start constructing teh PLSQL blcok

cat <<EOF > ${SQL_SCRIPT}

declare
   vx_xml XMLTYPE;
   vc_xml CLOB ;
begin
   vc_xml := '';   
EOF

cat ${XML_FILE} | fold -80 -s | sed "s/^/      vc_xml := vc_xml || '/" | sed "s/$/';/" >> ${SQL_SCRIPT}


cat <<EOF >> ${SQL_SCRIPT}

   vx_xml := XMLTYPE(vc_xml);
   call_the_stored_proc(vx_xml); -- A strored procedure to process the XML data as required
end;
/

EOF

cat ${SQL_SCRIPT}

cat <<EOF > sqlplus -S /nolog

conn un/pw@db

@${SQL_SCRIPT}

quit

EOF

Run this as follows:

bash the_script.sh the_source_data.xml

Answer 2

As you're in a windows and oracle environment, here's a powershell and sqlcl solution.

given some.xml file like.

<?xml version="1.0"?>
<root>
<listitem someattribute='thingOne'>content One</listitem>
<listitem someattribute='thingTwo'>content Two</listitem>
<listitem someattribute='thingThree'>content Three</listitem>
</root>

and a table MYTABLE like:

Name           Null?    Type               
-------------- -------- ------------------ 
COLUMNONE               VARCHAR2(25)         
COLUMNTWO               VARCHAR2(25)       

I'll use a PowerShell script (xml2csv.ps1) like:

#read from file
[xml]$inputFile = Get-Content -Path some.xml

#Walk through the records
$inputFile.root.ChildNodes | 
    # re-map to column names using ScriptBlock(s)
    Select-Object @{Name=’columnOne’; Expression={$_.someattribute}},@{Name=’columnTwo’; Expression={$_.'#text'}} |
        # write out CSV file
        Export-Csv -Path some.csv -NoTypeInformation -Encoding:UTF8

Run this as powershell.exe xml2csv.ps1

Load the database by using SQLcl (download from https://www.oracle.com/tools/downloads/sqlcl-downloads.html or included in SQL Developer) and run the LOAD command (https://www.thatjeffsmith.com/archive/2019/09/sqlcl-and-the-load-csv-command/).

Start a session sql.exe myuser/mypassword@MYDATABASE and execute:

LOAD MYTABLE some.csv