CODEWITHSUNDEEP
oracle-database
King
King

Reputation: 31

how to read the XML file from oracle?

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

Upvotes: 2

Views: 10797

Answers (2)

Tom Clark AaiCanHelp.com
Tom Clark AaiCanHelp.com

Reputation:

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.

Upvotes: 1

Traveling Tech Guy
Traveling Tech Guy

Reputation: 27811

You can always pass the XML as a VarChar parameter to the stored procedure - it is just a long string.

But ask yourself: do you really want to pass long strings and parse XMLs on the DB side? Stored procedures are meant to retrieve and manipulate data, not parse complex XMLs. Furthermore, you're just complicating your debugging process. I suggest parsing the XML on your app's side (web, client) and sending parsed data to the stored procedure.

Upvotes: 0

Related Questions