How to extract datas from XML file and insert into oracle tableLoad the xmlfile into a variable and use the following select to insert into the table:
SQL> select * from xmltable('*' passing xmltype(' <Employee_Details> <Employee> <emp_id>100</emp_id> <emp_name>Steven</emp_name> </Employee> <Employee> <emp_id>101</emp_id> <emp_name>Neena</emp_name> </Employee> <Employee> <emp_id>102</emp_id> <emp_name>Lex</emp_name> </Employee> <Employee> <emp_id>103</emp_id> <emp_name>Alexander</emp_name> </Employee> </Employee_Details>').extract('//Employee') columns id int path 'emp_id', name varchar2(10) path 'emp_name' ) x / ID NAME ---------- --------------- 100 Steven 101 Neena 102 Lex 103 Alexander 4 rows selected.
It makes it easier if it's valid XML.
i.e. no space in tag "Employee Details" and either emp_name as per starting tags or emp_no as per end tags, not both.
sorry it is not working in my machine.could please help me with XML sequence
"It is not working" is not very descriptive.
Read and memorize:
and add the details we need in order to help you.
Is your file located on your client machine or on the database server?
Are you getting an error? Then post the full code and message...
What database version are you working with?
This is the same question you asked on your previous thread:
Did you bother to read the responses?
XML Sequence is deprecated and you should use XMLTABLE instead.
Also you should specify your database version etc. when you ask questions as described in the FAQ:
Also consider that there is an XMLDB "space" specifically dedicated to asking XML based questions:
I have the same problem but I think your solution doesn't work on my db 'cause I work on 10gR2 Express Edition, so I'd need a script using XMLSEQUENCE and EXTRACT and EXTRACTVALUE functions.