how to read the XML file from oracle??

Question

I need to pass an XML file as an input parameter to a stored procedure. The procedure will read the XML file and insert the data into a table.

How can I perform the XML file read inside the stored procedure? Example code or reference links appreciated.

Thanks in advance

Answer 1

I personally don't like this one bit, but that aside, you can use the Oracle XDK for this.

Answer 2

Hi King,

One method to access an XML file from SQL is described in this SO question.

Answer 3

You can have your oracle procedure take an XML parameter and use sql to extract the information. If you are passing in multiple lines and want oracle to break them up and process them one line at a time there may be a performance hit. I have found that using the calling program to extract the xml and call the stored procedure over and over with each record was mush faster. Below is an example on XML parameters and extraction:

PROCEDURE ProcedureName(xml_i IN XMLTYPE)

SELECT DISTINCT EXTRACT(VALUE(level1), '//column_name1/text()').getNumberVal() AS column_name1 , EXTRACT(VALUE(level1), '//PathPart1/text()').getNumberVal() As column_name2 , EXTRACT(VALUE(level1), '// PathPart1/text()').getStringVal() AS column_name3 , Constants.no_c FROM xml_doc_l , TABLE(XMLSequence(EXTRACT(X.xml_doc, '/path3'))) level1;

I extracted and inserted the data into a global temporary table that I used for processing. Once processing was complete I would do a commit which would empty the temporary table automatically. I was processing 150k sql statements an hour with this process.

You will need to do some more research on the extract procedure and read oracle’s examples. It is somewhat cumbersome to follow. If you start with a very easy example it will make help you finalize your solution.