One of my blog readers asked me how to import data from XML file. There are lots of ways to do it but I’ll show only a simple one by using DBMS_XMLSTORE.
We will need a simple XML file to import. So I created a folder as “/oracle/xmltest” and a text file named (sample.xml) in this folder. Oracle should be able to access this folder and file, so check OS permissions of them and be sure that they are readable by oracle user.
I entered the followed data into my sample xml file:
<NAME>Milhouse Van Houten</NAME>
As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:
CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 ));