Oracle Export Import xml__oracle

Oracle exports import XML, which is exactly the server-side import export, and the client needs to be routed through the application to the server-side directory beforehand. General application will not be resolved on the server side, unless the DBA operation, as a learning look at the following test, test environment Oracle 9I version number, 9.0.2.1

To create a test table:
CREATE TABLE People
(
PERSONID VARCHAR2 () PRIMARY KEY,
NAME VARCHAR2 (20),
Address VARCHAR2 (60),
TEL VARCHAR2 (20),
FAX VARCHAR2 (20),
EMAIL VARCHAR2 (40)
);

XML file data, saved as People.xml, placed under the test directory under D disk:

<?xml version= "1.0"?>
<PEOPLE>
<person personid= "E01" >
<name>tony blair</name>
<address>10 Downing Street, London, uk</address>
<TEL> (061) 98765</tel><fax> (061) 98768</fax>
<EMAIL>blair@everywhere.com</EMAIL>
</PERSON>
<person personid= "E02" >
<name>bill clinton</name>
<address>white House, usa</address>
<TEL> (001) 6400 98765</tel><fax> (001) 6400 98769</fax>
<EMAIL>bill@everywhere.com</EMAIL>
</PERSON>
<person personid= "E03" >
<name>tom cruise</name>
<address>57 Jumbo Street, New York, usa</address>
<TEL> (001) 4500 67859</tel><fax> (001) 4500 67895</fax>
<EMAIL>cruise@everywhere.com</EMAIL>
</PERSON>
<person personid= "E04" >
<name>linda goodman</name>
<address>78 Crax Lane, London, uk</address>
<TEL> (061) 56789</tel><fax> (061) 56772</fax>
<EMAIL>linda@everywhere.com</EMAIL>
</PERSON>
</PEOPLE>

Import XML files to stored procedures in a datasheet:

/** *file_path XML file paths that need to be parsed such as: D:/test/people.xml *log_path The path of the file that saved the log as: D:/test/xmllog.txt **/CREATE OR REPLACE Procedur E Addperson (File_path varchar2,log_path VARCHAR2) as--//xml parser xmlpar xmlparser.parser: = Xmlparser. Newparser; --//dom Document Object Doc xmldom. DOMDocument; Len Integer; Personnodes xmldom. Domnodelist; Chilnodes xmldom. Domnodelist; Tempnode xmldom. Domnode; Temparrmap xmldom. Domnamednodemap; --================================--The following variables are used to get the value of the XML node PID VARCHAR2 (4); Name VARCHAR2 (50); Address VARCHAR2 (200); Tel Varchar2 (20); Fax varchar2 (20); Email varchar (100); TMP integer; --================================ BEGIN Xmlpar: = Xmlparser.newparser; Xmlparser.seterrorlog (Xmlpar, Log_path); Xmlparser.parse (Xmlpar, File_path); Doc: = Xmlparser.getdocument (Xmlpar); --Releasing the parser instance Xmlparser.freeparser (XMLPAR); --Get all person elements personnodes: = Xmldom.getelementsbytagname (doc, ' person '); Len: = Xmldom.getlength (personnodes); ---traverse all person elements for I in 0..len-1 loop--Get the first person Tempnode: = XMLdom.item (Personnodes, i); --All attributes Temparrmap: = Xmldom.getattributes (Tempnode); --Get PERSONID value pid: = Xmldom.getnodevalue (Xmldom.getnameditem (Temparrmap, ' PERSONID ')); --Get all child nodes chilnodes: = Xmldom.getchildnodes (Tempnode); --Gets the number of child nodes tmp: = xmldom. GetLength (Chilnodes); --Gets the value of each child node name: = Xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (chilnodes, 0)); Address: = Xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (chilnodes, 1)); Tel: = Xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (Chilnodes, 2)); Fax: = Xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (Chilnodes, 3)); Email: = Xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (Chilnodes, 4)); Insert data into people VALUES (pid,name,address,tel,fax,email); COMMIT; End LOOP; --Release Document Object Xmldom.freedocument (DOC); EXCEPTION when others THEN dbms_output. Put_Line (SQLERRM); End Addperson;

To execute a stored procedure:

Sql> call Addperson (' D:/test/people.xml ', ' d:/test/people.log ');

Method called

Sql> SELECT * from people;

PERSONID NAME address TEL FAX EMAIL
E01 Tony Blair Downing Street, London, UK (061) 98765 (061 98768) blair@everywhere.com
E02 Bill Clinton White House, USA (001) 6400 98765 (001) 6400 98769 bill@everywhere.com
E03 Tom Cruise Jumbo Street, New York, USA (001) 4500 67859 (001) 4500 67895 cruise@everywhere.com
E04 Linda Goodman Crax Lane, London, UK (061) 56789 061 (56772)

Exported stored procedures: Enter the export path is OK, the following is used to nest stored procedures, because there is no use of XML operations, not to delve into, but there is an article on the export made a more detailed description, reference http://weiwu83.javaeye.com/blog/151855

--The process of exporting data from a database into an XML file
Create or Replace procedure Db2xml (xmlfile varchar2) as
Doc Xmldom. DOMDocument;
RET XMLDOM. Domnode;
Peoplenode xmldom. Domnode;
--a cursor traversing the entire people table
Cursor cur_people is select * from people;
--Converts a row of records in a people table to an element
--and inserts into the DOM document the process of people the root node.
Procedure Addperson (Doc xmldom. Domdocument,people xmldom. Domnode,
V_pid varchar2,v_name varchar2,v_addr varchar2,
V_tel varchar2,v_fax varchar2,v_email varchar2)
Is
Personelem xmldom. DomElement;
Personnode xmldom. Domnode;
Itemelem xmldom. DomElement;
Itemnode xmldom. Domnode;
Text xmldom. Domtext;
Begin
--Create a person node
Personelem: = Xmldom.cre