Impress your friends with DBMS_XMLDOMFiled Under: Oracle
Oracle has a very interesting package to deal with the XML: DBMS_XMLDOM
I am still not very familiar with this package but with it, I loaded an XML file (multi-level structured) directly into a table. I have not tested on a large volume of data but I think it would be efficient.
I did my tests on an Oracle 9.2.0.4.0 under a CentOS Linux
Here is a very rudimentary procedure :
First, an XML file for the example :
< ?xml version="1.0" standalone="no"?> <racine> <family lastname="Smith"> <member memberid="m1">Sarah</member> <member memberid="m2">Bob</member> <member memberid="m3" mom="m1" dad="m2">Joanne</member> <member memberid="m4" mom="m1" dad="m2">Jim</member> </family> <family lastname="Dupont"> <member memberid="m1">Jean</member> <member memberid="m2">Robert</member> <member memberid="m3" mom="m1" dad="m2">John</member> <member memberid="m4" mom="m1" dad="m2">Gary</member> </family> </racine>
Then, I created a table in the database :
CREATE TABLE personnes (NOM VARCHAR2(60), Prenom VARCHAR2(60));
And now, the entire procedured I used to load the data :
CREATE OR REPLACE PROCEDURE chargementXML(dir VARCHAR2, inpfile VARCHAR2, errfile VARCHAR2) IS p xmlparser.parser; doc xmldom.DOMDocument; PROCEDURE insertElements(doc xmldom.DOMDocument) IS nl xmldom.DOMNodeList; len NUMBER; n xmldom.DOMNode; nnm xmldom.DOMNamedNodeMap; family VARCHAR2(60); myElement xmldom.DOMElement; nodeValue VARCHAR2(60); BEGIN -- get all elements nl := xmldom.getElementsByTagName(doc, '*'); len := xmldom.getLength(nl); -- loop through elements FOR i IN 0..len-1 LOOP n := xmldom.item(nl, i); IF UPPER(xmldom.getNodeName(n))= 'FAMILY' THEN myElement := xmldom.makeElement(n); family := xmldom.getAttribute(myElement ,'lastname'); END IF; -- get the text node associated with the element node n := xmldom.getFirstChild(n); IF xmldom.getNodeType(n) = xmldom.TEXT_NODE THEN nodeValue := xmldom.getNodeValue(n); INSERT INTO personnes VALUES (family,nodeValue); END IF; END LOOP; END insertElements; BEGIN p := xmlparser.newParser; -- Paramètrages xmlparser.setValidationMode(p, FALSE); xmlparser.setErrorLog(p, dir || '/' || errfile); xmlparser.setBaseDir(p, dir); -- Fichier source xmlparser.parse(p, dir || '/' || inpfile); -- Document doc := xmlparser.getDocument(p); -- Insertions des elements insertElements(doc); END chargementXML; /
This is the new contents of the table :
SQL> SELECT * FROM personnes; NOM ------------------------------------------------------------ PRENOM ------------------------------------------------------------ Smith Sarah Smith Bob Smith Joanne NOM ------------------------------------------------------------ PRENOM ------------------------------------------------------------ Smith Jim Dupont Jean Dupont Robert NOM ------------------------------------------------------------ PRENOM ------------------------------------------------------------ Dupont John Dupont Gary 8 rows selected.
With the documentation on oracle.com :http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_xmldo5.htm#1024279 , I’m sure that it’s possible to do fabulous things
- Permalien
- guroot
- 09:11 PM
- Comments(0)