Oracle stored procedure parse XML, and the data persistence

2011-03-14  来源:本站原创  分类:Database  人气:90 

Need to parse the xml file: people.xml
1 <? Xml version = "1.0"?>
2 <PEOPLE>
3 <PERSON PERSONID="E01">
4 <NAME> Tony Blair </ NAME>
5 <ADDRESS> 10 Downing Street, London, UK </ ADDRESS>
6 <TEL> (061) 98765 </ TEL>
7 <FAX> (061) 98768 </ FAX>
8 <EMAIL> [email protected] </ EMAIL>
9 </ PERSON>
10 <PERSON PERSONID="E02">
11 <NAME> Bill Clinton </ NAME>
12 <ADDRESS> White House, USA </ ADDRESS>
13 <TEL> (001) 6400 9876 5 </ TEL>
14 <FAX> (001) 6400 9876 9 </ FAX>
15 <EMAIL> [email protected] </ EMAIL>
16 </ PERSON>
17 <PERSON PERSONID="E03">
18 <NAME> Tom Cruise </ NAME>
19 <ADDRESS> 57 Jumbo Street, New York, USA </ ADDRESS>
20 <TEL> (001) 4500 6785 9 </ TEL>
21 <FAX> (001) 4500 6789 5 </ FAX>
22 <EMAIL> [email protected] </ EMAIL>
23 </ PERSON>
24 <PERSON PERSONID="E04">
25 <NAME> Linda Goodman </ NAME>
26 <ADDRESS> 78 Crax Lane, London, UK </ ADDRESS>
27 <TEL> (061) 54 56789 </ TEL>
28 <FAX> (061) 54 56772 </ FAX>
29 <EMAIL> [email protected] </ EMAIL>
30 </ PERSON>
31 </ PEOPLE>

I have to scott user, for example, the new table:
1 CREATE TABLE PEOPLE
2 (
3 PERSONID VARCHAR2 (4) PRIMARY KEY,
4 NAME VARCHAR2 (50),
5 ADDRESS VARCHAR2 (200),
6 TEL VARCHAR2 (20),
7 FAX VARCHAR2 (20),
8 EMAIL VARCHAR2 (100)
9);

Resolve and persistence of the stored procedure code:
1 / **
2 * file_path need to parse the XML file path as: D: \ OracleTest \ people.xml
3 * log_path save the log file path as: D: \ OracleTest \ xmllog.txt
4 ** /
5
6 CREATE OR REPLACE PROCEDURE addPerson (file_path VARCHAR2, log_path VARCHAR2)
7 AS
8 - / / XML parser
9 xmlPar XMLPARSER.parser: = XMLPARSER.NEWPARSER;
10 - / / DOM document object
11 doc xmldom.DOMDocument;
12 len integer;
13 personNodes xmldom.DOMNodeList;
14 chilNodes xmldom.DOMNodeList;
15 tempNode xmldom.DOMNode;
16 tempArrMap xmldom.DOMNamedNodeMap;
17 --================================
18 - The following variables are used to obtain the value of the XML node
19 pid varchar2 (4);
20 name varchar2 (50);
21 address varchar2 (200);
22 tel varchar2 (20);
23 fax varchar2 (20);
24 email varchar (100);
25 tmp integer;
26 --================================
27 BEGIN
28 xmlPar: = xmlparser.newParser;
29 xmlparser.setErrorLog (xmlPar, log_path);
30 xmlparser.parse (xmlPar, file_path);
31 doc: = xmlparser.getDocument (xmlPar);
32-- release parser instance
33 xmlparser.freeParser (xmlPar);
34 - Get all PERSON elements
35 personNodes: = xmldom.getElementsByTagName (doc, 'PERSON');
36 len: = xmldom.getLength (personNodes);
37 - through all PERSON elements
38 FOR i in 0 .. len-1
39 LOOP
40 - Get the first i-PERSON
41 tempNode: = xmldom.item (personNodes, i);
42 - All properties
43 tempArrMap: = xmldom.getAttributes (tempNode);
44 - get value of the PERSONID
45 pid: = xmldom.getNodeValue (xmldom.getNamedItem (tempArrMap, 'PERSONID'));
46 - for sub elements
47 chilNodes: = xmldom.getChildNodes (tempNode);
48 tmp: = xmldom.GETLENGTH (chilNodes);
49 name: = xmldom.getNodeValue (xmldom.getFirstChild (xmldom.item (chilNodes, 0)));
50 address: = xmldom.getNodeValue (xmldom.getFirstChild (xmldom.item (chilNodes, 1)));
51 tel: = xmldom.getNodeValue (xmldom.getFirstChild (xmldom.item (chilNodes, 2)));
52 fax: = xmldom.getNodeValue (xmldom.getFirstChild (xmldom.item (chilNodes, 3)));
53 email: = xmldom.getNodeValue (xmldom.getFirstChild (xmldom.item (chilNodes, 4)));
54 - Insert Data
55 INSERT INTO PEOPLE VALUES (pid, name, address, tel, fax, email);
56 COMMIT;
57 END LOOP;
58 - the release of the document object
59 xmldom.freeDocument (doc);
60 EXCEPTION
61 WHEN OTHERS THEN
62 DBMS_output.PUT_LINE (SQLERRM);
63 END addPerson;

Many friends say that the implementation error, and now I use my screenshots in Oracle9i posted for reference:

相关文章
  • Oracle stored procedure parse XML, and the data persistence 2011-03-14

    Need to parse the xml file: people.xml 1 <? Xml version = "1.0"?> 2 <PEOPLE> 3 <PERSON PERSONID="E01"> 4 <NAME> Tony Blair </ NAME> 5 <ADDRESS> 10 Downing Street, London, UK </ ADDRESS> 6 <T

  • Write Oracle stored procedure parsing XML, and the data persistence 2010-03-15

    Need to parse xml file: people.xml 1 <? Xml version = "1.0"?> 2 <PEOPLE> 3 <PERSON PERSONID="E01"> 4 <NAME> Tony Blair </ NAME> 5 <ADDRESS> 10 Downing Street, London, UK </ ADDRESS> 6 <TEL&g

  • JAVA Oracle stored procedure input parameter in the LIST 2010-11-28

    Today, they want nothing to do over the weekend to try a combination of JAVA and ORACLE extent, the results reflect the combination of these two is really very powerful weapon, never been done before, or since. Hey ~ ~ JAVA for the weekend to help my

  • atf project - using oracle stored procedure to repair the data in June 2009-07-17

    atf project - using oracle stored procedure to repair the data in June a total of nearly twenty thousand pieces of data Step 1. The first execution temp_table Step 2 implementation ticket_0601_prc, t_fee_clear_list_0601_prc, debtor_clear_list_0601_pr

  • Oracle stored procedure 2010-03-30

    1. Overview Oracle stored procedure development key points: • Use Notepad text editor, using Oracle PL / SQL programming language to write a stored procedure; • In the Oracle database to create a stored procedure; • In the Oracle database using SQL *

  • basic syntax oracle stored procedure 2010-03-29

    Keywords: oracle 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select

  • oracle stored procedure syntax 2010-03-30

    1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELECT INTO STATEMENT To select the query results

  • The basic syntax of oracle stored procedure and precautions 2010-03-31

    The basic syntax of oracle stored procedure 1. The basic structure CREATE OR REPLACE PROCEDURE stored procedure name ( Parameter 1 IN NUMBER, Parameter 2 IN NUMBER ) IS Variable 1 INTEGER: = 0; Variable 2 DATE; BEGIN END stored procedure name 2.SELEC

  • ORACLE stored procedure in the asynchronous call 2010-04-09

    ORACLE stored procedure in the asynchronous call Analysis: Wang Hui Source: SAN Published: 2003.05.26 1. About the Author Wang Hui work since 1994, has served as teacher, database administrators, the main programmer, project manager for a company as

  • C # How to call oracle stored procedure in the most simple example 2010-04-26

    C # calling oracle stored procedure example of the simplest Oracle side 1. Create Oracle stored procedure create or replace procedure proce_test (paramin in varchar2, paramout out varchar2, paraminout in out varchar2) as varparam varchar2 (28); begin

  • Oracle stored procedure to learn 2010-05-14

    The basic syntax of oracle stored procedure Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the v

  • learning oracle stored procedure 2010-05-15

    Create the stored procedure syntax: create or replace procedure stored procedure name (param1 in type, param2 out type) as Variable 1 type (range); Variable 2 type (range); Begin Select count (*) into the variable 1 from table A where column name = p

  • Oracle stored procedure and cursor 2010-06-02

    Oracle stored procedure and cursor Oracle stored procedure and cursor: select myFunc (parameter 1, parameter 2 ..) to dual; - one can perform some business logic: Oracle functions and stored procedures in the difference between: A: function must retu

  • Call oracle stored procedure with java Summary 2010-06-09

    1, what is stored procedure. Stored procedure is a section of the database server-side program, it has two types. A kind of similar to the SELECT query used to retrieve the data, the data can be retrieved in the form of a data set returned to the cus

  • Oracle stored procedure in the preparation of lessons learned 2010-06-23

    1, developers use other libraries if the Table or View, be sure to create a View in the current database to cross-database operations, it is best not to directly use the "databsevv.dbo.table_name", because it can not show that the SP sp_depends

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • Oracle stored procedure execution time 2 methods (transfer) 2010-07-21

    Today, we will describe the implementation of Oracle stored procedures regularly mainly two kinds of methods of approach, willing to learn from Oracle stored procedure in your implementation of the actual operation time to play on this subject. If yo

  • Oracle stored procedure syntax to learn (Procedure example 1) 2010-07-26

    Stored procedure consists of three parts: a statement, the operative part, abnormal. Procedures and whether the parameter can be parameterized stored procedures. No reference procedure syntax 1 create or replace procedure NoParPro 2 as; 3 begin 4; 5

  • Oracle stored procedure using temporary tables 2010-10-08

    Oracle stored procedure using temporary tables One, Oracle temporary table knowledge In Oracle, the temporary table into SESSION, TRANSACTION two, SESSION-level data in the temporary table exists in the entire SESSION until the end of the SESSION; an

  • Oracle stored procedure [Back Qinbo comprehensive Detailed] 2010-10-13

    Oracle stored procedures Keywords: storedprocedure 1, What is stored procedure. A stored procedure is a database server-side program, it has two types. A similar SELECT query used to retrieve the data, the data can be retrieved in the form of a data