Using XML DB

All right. I got my Oracle 12c enterprise database installed. Now I got a big XML file I want to parse. Extract out the data. Stick everything in a database table or two. But how the heck do you do that? I am used to writing client/server programs that could do this. However this time around, I want to do it in the database.

Well I can declare an XMLType variable. Then for a quick and dirty, put the XML text in the constructor. Use the Extract function to dig down into a certain XPath in the data. Then use the ExtracValue function to get fields. Iterate over the whole XML document. Insert data into a real table. Done. Right?

This hack worked for a small data set of three records. Happy with that success, I jammed 5k records into the XMLType variable. Ran the code. It bombed. Not even sure what the error was. Because the whole sqlplus program blew up. Obviously I was doing something wrong here.

Okay. Let's declare a CLOB variable instead. Read the XML file into the CLOB. Then parse this XML. This feels better. It feels right. I can call selectNodes. That gives me a DOMNodeList. Iterating through that list gives me the data I want. No trouble throwing 5k records at that. I now have a table full of data.

Next up, I need to work that data.