So long story short, I receive XML files from an SFTP client and I need to store this data in an oracle database.
I'm currently deserializing the XML file and generating one large insert statement with all the values, but inserting this takes forever (30-50 minutes for 120000 rows).
I don't want to save the files on the server, so I'd rather use the FileStream
data
Currently I read the XML files like this:
using (SftpFileStream stream = sftp.OpenRead(filename))
{
Xml.Process(stream);
}
So I want to try XMLTABLE
to insert the XML file.
Let's say I didn't create a new table for the data yet and I received the stream data. I have the following XML file, which is called line.xml
:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!--Created by a179-main 0.78.24-->
<cern:lines creationDate="2018-03-16T19:36:05" xmlns:cern="http://www.website.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.website.com ../ns/infrastructure.xsd">
<cern:line id="10" validFromDate="1996-06-02" validToDate="2078-12-31"/>
<cern:line id="11" validFromDate="1997-08-03" validToDate="2078-12-31"/>
<cern:line id="12" validFromDate="1997-08-03" validToDate="2078-12-31"/>
</cern:lines>
How would I have to insert this into the database using XMLTABLE?
Also, what table columns do I create?
And is it possible to attach the stream or streamdata in the XMLTABLE statement?
Extra: I'm currently using Dapper as an ORM. Pretty much the same as ADO.NET, but a bit easier to read.