How to save XML file in PL/SQL database

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

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