-
read xml to table
hi
below is a sample xml data i have. i want to read this file and store it into a table using oracle procedure.
< Access>
< User email="[email protected]" phone="+44 (0) 999 999 9999" first_name="fname" name="fname lname" last_name="lname">
< BasicSignon>loginA< / BasicSignon>
< Userclasses>
< Userclass name="Site User" />
< Userclass name="European Admin" />
< Userclass name="Sales Admin" />
< / Userclasses>
< / User >
< / Access>
i tried something similar to as in http://www.oracle-base.com/articles/...ocuments8i.php. However i didnot find a way to process the child nodes and also the attibutes of the nodes (like email, phone in User node).
if anyone got an example source, please help me. thanks in advance.
-sudhi
-
Hi.
I don't have an 8i instance to work on so I've used 10g, and used the contents of the 9i version of that article as a base. See:
http://www.oracle-base.com/articles/...ocuments9i.php
Anyway, I saved your XML to a file called users.xml and ran this code.
Code:
CREATE DIRECTORY xml_dir AS 'c:\';
SET SERVEROUTPUT ON
DECLARE
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_attrs dbms_xmldom.DOMNamedNodeMap;
l_attr dbms_xmldom.DOMNode;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_text VARCHAR2(32767);
BEGIN
l_bfile := BFileName('XML_DIR', 'users.xml');
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
dbms_lob.close(l_bfile);
-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
-- Create a parser.
l_parser := dbms_xmlparser.newParser;
-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
-- Free resources associated with the Parser now it is no longer needed.
dbms_xmlparser.freeParser(l_parser);
l_n := dbms_xslprocessor.selectSingleNode(xmldom.makeNode(l_doc),'/Access/User');
l_attrs := dbms_xmldom.getattributes(l_n);
FOR cur_attr IN 0 .. dbms_xmldom.getLength(l_attrs) - 1 LOOP
l_attr := dbms_xmldom.item(l_attrs, cur_attr);
DBMS_OUTPUT.put_line(dbms_xmldom.getNodeName(l_attr) || '=' || dbms_xmldom.getNodeValue(l_attr));
END LOOP;
l_n := dbms_xslprocessor.selectSingleNode(xmldom.makeNode(l_doc),'/Access/User/BasicSignon');
DBMS_OUTPUT.put_line(dbms_xmldom.getNodeName(l_n) || '=' || dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(l_n)));
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(xmldom.makeNode(l_doc),'/Access/User/Userclasses/Userclass');
-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_uc IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, cur_uc);
l_attrs := dbms_xmldom.getattributes(l_n);
l_attr := dbms_xmldom.item(l_attrs, 0);
DBMS_OUTPUT.put_line(dbms_xmldom.getNodeName(l_attr) || '=' || dbms_xmldom.getNodeValue(l_attr));
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);
END;
/
When I run this I get this:
Code:
[email protected]
phone=+44 (0) 999 999 9999
first_name=fname
name=fname lname
last_name=lname
BasicSignon=loginA
name=Site User
name=European Admin
name=Sales Admin
PL/SQL procedure successfully completed.
I'm sure you could chop some of the crap out, but I'm a bit rushed for time.
Cheers
Tim...
-
many many thanks.
regards,
Sudhi
-
Hi.
Remember, there are several ways of dealing with XML including:
- Search it with XPATH.
- Manually traverse the DOM tree.
- Convert it to an XML type and query the contents with SQL.
- Use XMLDB to load the data into a table for you.
The options you can pick depend on which of Oracle you are using along with your preferences. These articles might help:
http://www.oracle-base.com/articles/...cles9i.php#XML
Cheers
Tim...
-
hi
I am not getting there again...
XML file (i have put spaces in the open and close tags here to display properly in this forum)
Code:
< ? xml version="1.0" encoding="UTF-8" standalone="no" ? >
< Access port="4355" filter="all" host="localhost" type="users" user="Administrator" baseDN="o=cn, c=cb" namespace="default" >
< User email="[email protected]" phone="+99 999 999999" first_name="" name="Administrator" last_name="" >
< BasicSignon >Administrator< / BasicSignon>
< OSSignon>devadmin< / OSSignon>
< Userclasses >
< Userclass name="Root User Class" / >
< / Userclasses >
< / User>
< / Access>
My procedure
Code:
CREATE OR REPLACE PROCEDURE zbis_users_load
AS
l_bfile BFILE;
l_clob CLOB;
l_parser xmlparser.parser;
l_doc xmldom.domdocument;
l_attrs xmldom.domnamednodemap;
l_attr xmldom.domnode;
l_nl xmldom.domnodelist;
l_n xmldom.domnode;
l_text VARCHAR2 (32767);
lemail VARCHAR2 (400);
lphone VARCHAR2 (400);
lfirstname VARCHAR2 (400);
lname VARCHAR2 (400);
llastname VARCHAR2 (400);
lbasicsignon VARCHAR2 (400);
lossignon VARCHAR2 (400);
luserclass VARCHAR2 (400);
BEGIN
l_bfile := BFILENAME ('LOG', 'users.xml');
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_LOB.createtemporary (l_clob, CACHE => FALSE);
DBMS_LOB.OPEN (l_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile (l_clob, l_bfile, DBMS_LOB.getlength (l_bfile), 1,1);
DBMS_LOB.CLOSE (l_bfile);
-- make sure implicit date conversions are performed correctly
DBMS_SESSION.set_nls ('NLS_DATE_FORMAT', '''DD-MON-YYYY''');
-- Create a parser.
l_parser := xmlparser.newparser;
-- Parse the document and create a new DOM document.
xmlparser.parseclob (l_parser, l_clob);
l_doc := xmlparser.getdocument (l_parser);
-- Free resources associated with the Parser now it is no longer needed.
xmlparser.freeparser (l_parser);
l_n := xslprocessor.selectsinglenode (xmldom.makenode (l_doc), '/Access/User');
l_attrs := xmldom.getattributes (l_n);
FOR cur_attr IN 0 .. xmldom.getlength (l_attrs) - 1
LOOP
l_attr := xmldom.ITEM (l_attrs, cur_attr);
IF xmldom.getnodename (l_attr) = 'email' THEN
lemail := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'phone' THEN
lphone := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'first_name' THEN
lfirstname := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'name' THEN
lname := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'last_name' THEN
llastname := xmldom.getnodevalue (l_attr);
END IF;
--DBMS_OUTPUT.put_line(xmldom.getNodeName(l_attr) || '=' || xmldom.getNodeValue(l_attr));
END LOOP;
l_n := xslprocessor.selectsinglenode (xmldom.makenode (l_doc), '/Access/User/BasicSignon');
lbasicsignon := xmldom.getnodevalue (xmldom.getfirstchild (l_n));
--DBMS_OUTPUT.put_line(xmldom.getNodeName(l_n) || '=' || xmldom.getNodeValue(xmldom.getFirstChild(l_n)));
l_n := xslprocessor.selectsinglenode (xmldom.makenode (l_doc), '/Access/User/OSSignon');
lossignon := xmldom.getnodevalue (xmldom.getfirstchild (l_n));
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := xslprocessor.selectnodes (xmldom.makenode (l_doc), '/Access/User/Userclasses/Userclass');
-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_uc IN 0 .. xmldom.getlength (l_nl) - 1
LOOP
l_n := xmldom.ITEM (l_nl, cur_uc);
l_attrs := xmldom.getattributes (l_n);
l_attr := xmldom.ITEM (l_attrs, 0);
luserclass := xmldom.getnodevalue (l_attr);
-- DBMS_OUTPUT.put_line(xmldom.getNodeName(l_attr) || '=' || xmldom.getNodeValue(l_attr));
INSERT INTO zusers
(basic_signon, os_signon, first_name, last_name,
disp_name, user_email, user_phone, user_class
)
VALUES (lbasicsignon, lossignon, lfirstname, llastname,
lname, lemail, lphone, luserclass
);
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
xmldom.freedocument (l_doc);
END;
/
The error I get is
SQL> exec zbis_users_load
ORA-0000: normal, successful completion
BEGIN zbis_users_load; END;
*
ERROR at line 1:
ORA-20100: Error occurred while parsing: Start of root element expected.
ORA-06512: at "SYS.XMLPARSER", line 22
ORA-06512: at "SYS.XMLPARSER", line 103
ORA-06512: at "ADDPM.ZBIS_USERS_LOAD", line 44
ORA-06512: at line 1
regards,
Sudhi
-
-
with the code TimHall given, I got error with below lines
Code:
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
ERROR at line 17:
ORA-06550: line 17, column 3:
PLS-00306: wrong number or types of arguments in call to 'LOADFROMFILE'
When I changed the line to
Code:
dbms_lob.loadFromFile(l_clob,
l_bfile,
dbms_lob.getLength(l_bfile),1,1);
I got error
ERROR at line 35:
ORA-06550: line 35, column 45:
PLS-00306: wrong number or types of arguments in call to 'MAKENODE'
Is this something not installed on my database ?
regards,
Sudhi
-
Hi.
Please post everythig to run the example in future. You did not provide the table or the directory object.
I ran the following ant it worked fine:
Code:
DROP TABLE zusers;
CREATE TABLE zusers (
basic_signon VARCHAR2(400),
os_signon VARCHAR2(400),
first_name VARCHAR2(400),
last_name VARCHAR2(400),
disp_name VARCHAR2(400),
user_email VARCHAR2(400),
user_phone VARCHAR2(400),
user_class VARCHAR2(400)
);
CREATE OR REPLACE DIRECTORY log AS 'c:\';
The procedure I ran is basically the same as yours:
Code:
CREATE OR REPLACE PROCEDURE zbis_users_load
AS
l_bfile BFILE;
l_clob CLOB;
l_parser xmlparser.parser;
l_doc xmldom.domdocument;
l_attrs xmldom.domnamednodemap;
l_attr xmldom.domnode;
l_nl xmldom.domnodelist;
l_n xmldom.domnode;
l_text VARCHAR2 (32767);
l_email VARCHAR2 (400);
l_phone VARCHAR2 (400);
l_first_name VARCHAR2 (400);
l_name VARCHAR2 (400);
l_last_name VARCHAR2 (400);
l_basic_signon VARCHAR2 (400);
l_os_signon VARCHAR2 (400);
l_userclass VARCHAR2 (400);
BEGIN
l_bfile := BFILENAME ('LOG', 'users.xml');
--DBMS_OUTPUT.put_line (SQLERRM);
DBMS_LOB.createtemporary (l_clob, CACHE => FALSE);
DBMS_LOB.OPEN (l_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile (l_clob, l_bfile, DBMS_LOB.getlength (l_bfile), 1,1);
DBMS_LOB.CLOSE (l_bfile);
-- make sure implicit date conversions are performed correctly
DBMS_SESSION.set_nls ('NLS_DATE_FORMAT', '''DD-MON-YYYY''');
-- Create a parser.
l_parser := xmlparser.newparser;
-- Parse the document and create a new DOM document.
xmlparser.parseclob(l_parser, l_clob);
l_doc := xmlparser.getdocument(l_parser);
-- Free resources associated with the Parser now it is no longer needed.
xmlparser.freeparser (l_parser);
l_n := xslprocessor.selectsinglenode(xmldom.makenode (l_doc), '/Access/User');
l_attrs := xmldom.getattributes(l_n);
FOR cur_attr IN 0 .. xmldom.getlength (l_attrs) - 1
LOOP
l_attr := xmldom.ITEM (l_attrs, cur_attr);
IF xmldom.getnodename (l_attr) = 'email' THEN
l_email := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'phone' THEN
l_phone := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'first_name' THEN
l_first_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'name' THEN
l_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'last_name' THEN
l_last_name := xmldom.getnodevalue (l_attr);
END IF;
END LOOP;
l_n := xslprocessor.selectsinglenode(xmldom.makenode (l_doc), '/Access/User/BasicSignon');
l_basic_signon := xmldom.getnodevalue(xmldom.getfirstchild (l_n));
--DBMS_OUTPUT.put_line(xmldom.getNodeName(l_n) || '=' || xmldom.getNodeValue(xmldom.getFirstChild(l_n)));
l_n := xslprocessor.selectsinglenode(xmldom.makenode (l_doc), '/Access/User/OSSignon');
l_os_signon := xmldom.getnodevalue(xmldom.getfirstchild (l_n));
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := xslprocessor.selectnodes(xmldom.makenode (l_doc), '/Access/User/Userclasses/Userclass');
-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_uc IN 0 .. xmldom.getlength (l_nl) - 1
LOOP
l_n := xmldom.item(l_nl, cur_uc);
l_attrs := xmldom.getattributes(l_n);
l_attr := xmldom.item(l_attrs, 0);
l_userclass := xmldom.getnodevalue(l_attr);
DBMS_OUTPUT.put_line('l_basic_signon=' || l_basic_signon);
DBMS_OUTPUT.put_line('l_os_signon=' || l_os_signon);
DBMS_OUTPUT.put_line('l_first_name=' || l_first_name);
DBMS_OUTPUT.put_line('l_last_name=' || l_last_name);
DBMS_OUTPUT.put_line('l_name=' || l_name);
DBMS_OUTPUT.put_line('l_email=' || l_email);
DBMS_OUTPUT.put_line('l_phone=' || l_phone);
DBMS_OUTPUT.put_line('l_userclass=' || l_userclass);
INSERT INTO zusers
(basic_signon, os_signon, first_name, last_name,
disp_name, user_email, user_phone, user_class
)
VALUES (l_basic_signon, l_os_signon, l_first_name, l_last_name,
l_name, l_email, l_phone, l_userclass
);
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
xmldom.freedocument (l_doc);
END;
/
The results were:
Code:
SQL> exec zbis_users_load
l_basic_signon=Administrator
l_os_signon=devadmin
l_first_name=
l_last_name=
l_name=Administrator
[email protected]
l_phone=+99 999 999999
l_userclass=Root User Class
PL/SQL procedure successfully completed.
SQL>
I was not able to reproduce your issue. I'm running on 10g. Perhaps this is the difference. I don't have any 8i or 9i instances to dabble with, so if this is the problem I won't be of much use I guess.
Cheers
Tim...
-
thanks.
Seems the oracle version is the problem for me then. I get
SQL> exec Zbis_Users_Load
BEGIN Zbis_Users_Load; END;
*
ERROR at line 1:
ORA-20100: Error occurred while parsing: Start of root element expected.
ORA-06512: at "SYS.XMLPARSER", line 22
ORA-06512: at "SYS.XMLPARSER", line 103
ORA-06512: at "ADDPM.ZBIS_USERS_LOAD", line 35
ORA-06512: at line 1
regards,
Sudhi
-
Hi
I got almost right this time, but not. The problem earlier reported was to do with CLOB. It is reading fine now, however its not linking the child nodes correctly to its parents.
below is what I have done
Table
Code:
CREATE TABLE ZBIS_USERS
(
BASIC_SIGNON VARCHAR2(30 ),
OS_SIGNON VARCHAR2(30 ),
FIRST_NAME VARCHAR2(100 ),
LAST_NAME VARCHAR2(100 ),
DISP_NAME VARCHAR2(100 ),
USER_EMAIL VARCHAR2(100 ),
USER_PHONE VARCHAR2(100 ),
USER_CLASS VARCHAR2(100 )
)
XML
Code:
< ?xml version="1.0" encoding="UTF-8" standalone="no" ? >
< NamespaceReport>
< User email="[email protected]" phone="+99999999" first_name="" name="Administrator" last_name="">
< BasicSignon>Administrator< /BasicSignon>
< OSSignon>admin< /OSSignon>
< Userclasses>
< Userclass name="Root User Class" />
< /Userclasses>
< /User>
< User email="[email protected]" phone="888888" first_name="name1" name="name1 name2" last_name="name2">
< BasicSignon>nameA< /BasicSignon>
< Userclasses>
< Userclass name="Others" />
< Userclass name="Queries" />
< Userclass name="Report" />
< /Userclasses>
< /User>
< /NamespaceReport >
Source:
Code:
CREATE OR REPLACE PROCEDURE Zbis_Users_Load
AS
l_parser xmlparser.parser;
l_doc xmldom.domdocument;
l_attrs xmldom.domnamednodemap;
l_attr xmldom.domnode;
l_nl xmldom.domnodelist;
l_n xmldom.domnode;
l_text VARCHAR2 (32767);
l_email VARCHAR2 (400);
l_phone VARCHAR2 (400);
l_first_name VARCHAR2 (400);
l_name VARCHAR2 (400);
l_last_name VARCHAR2 (400);
l_basic_signon VARCHAR2 (400);
l_os_signon VARCHAR2 (400);
l_userclass VARCHAR2 (400);
BEGIN
DELETE FROM ZBIS_USERS;
-- make sure implicit date conversions are performed correctly
DBMS_SESSION.set_nls ('NLS_DATE_FORMAT', '''DD-MON-YYYY''');
-- Create a parser.
l_parser := xmlparser.newparser;
-- Parse the document and create a new DOM document.
xmlparser.parse (l_parser, 'd:\all_users.xml');
l_doc := xmlparser.getdocument (l_parser);
-- Free resources associated with the Parser now it is no longer needed.
xmlparser.freeparser (l_parser);
-- Get a list of all the user nodes in the document using the XPATH syntax.
l_nl := xslprocessor.selectnodes (xmldom.makenode (l_doc),'/NamespaceReport/User');
FOR cur_user IN 0 .. xmldom.getlength (l_nl) - 1 LOOP
l_n := xmldom.ITEM (l_nl, cur_user);
l_attrs := xmldom.getattributes (l_n);
FOR cur_attr IN 0 .. xmldom.getlength (l_attrs) - 1 LOOP
l_attr := xmldom.ITEM (l_attrs, cur_attr);
IF xmldom.getnodename (l_attr) = 'email' THEN
l_email := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'phone' THEN
l_phone := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'first_name' THEN
l_first_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'name' THEN
l_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'last_name' THEN
l_last_name := xmldom.getnodevalue (l_attr);
END IF;
END LOOP;
l_n := xslprocessor.selectsinglenode (xmldom.makenode (l_doc),'/NamespaceReport/User/BasicSignon');
l_basic_signon := xmldom.getnodevalue (xmldom.getfirstchild (l_n));
l_n := xslprocessor.selectsinglenode (xmldom.makenode (l_doc),'/NamespaceReport/User/OSSignon');
l_os_signon := xmldom.getnodevalue (xmldom.getfirstchild (l_n));
-- Get a list of all the user nodes in the document using the XPATH syntax.
l_nl := xslprocessor.selectnodes(xmldom.makenode (l_doc),'/NamespaceReport/User/Userclasses/Userclass');
-- Loop through the list and create a new record in a table collection
-- for each user record.
FOR cur_uc IN 0 .. xmldom.getlength (l_nl) - 1 LOOP
l_n := xmldom.ITEM (l_nl, cur_uc);
l_attrs := xmldom.getattributes (l_n);
l_attr := xmldom.ITEM (l_attrs, 0);
l_userclass := xmldom.getnodevalue (l_attr);
INSERT INTO ZBIS_USERS
(basic_signon, os_signon, first_name, last_name, disp_name,
user_email, user_phone, user_class)
VALUES (l_basic_signon, l_os_signon, l_first_name,
l_last_name, l_name, l_email, l_phone,l_userclass);
END LOOP;
END LOOP;
COMMIT;
-- Free any resources associated with the document now it
-- is no longer needed.
xmldom.freedocument (l_doc);
END;
/
help please.
regards,
sudhi
-
Look. You've now changed the file format now. You have a different root element and you have two user elements. There was no mention of the latter. I was under the assumption there was only one and as a result I wrote the example to only expect one.
I'm sorry if this sounds crappy, but I'm not going to look at this further until you send a realistic file. If you're going to change the goal posts half way through I can't be expected to take the correct approach.
Please, please, please as the question you want answered, not some hybrid thing that causes me to go off on a different path.
Please post a file in the format you expect to use. If you expect there to be multiples of any element, show then in the file like that.
Cheers
Tim...
-
sorry Tim,
the file format current one is the final. I was thinking, if I get to read one user element, i can place that in a loop and get n numbers of the same. however, i am not able to get that.
regards,
sudhi
-
What version of Oracle are you running? I'm guessing it's 8i.
The APIs have altered between 8i and 10g. As a result I don't know if I can write and test an example that will work for you.
Please confirm your version.
Cheers
Tim...
-
the script i posted in previous thread reads the xml file. so the API should work. i am using 9i
regards,
sudhi
-
OK.
This works for me:
Code:
CREATE OR REPLACE PROCEDURE zbis_users_load
AS
l_bfile BFILE;
l_clob CLOB;
l_parser xmlparser.parser;
l_doc xmldom.domdocument;
l_attrs xmldom.domnamednodemap;
l_attr xmldom.domnode;
l_users xmldom.domnodelist;
l_user xmldom.domnode;
l_nl xmldom.domnodelist;
l_n xmldom.domnode;
l_email zbis_users.user_email%TYPE;
l_phone zbis_users.user_phone%TYPE;
l_first_name zbis_users.first_name%TYPE;
l_name zbis_users.disp_name%TYPE;
l_last_name zbis_users.last_name%TYPE;
l_basic_signon zbis_users.basic_signon%TYPE;
l_os_signon zbis_users.os_signon%TYPE;
l_userclass zbis_users.user_class%TYPE;
BEGIN
l_bfile := BFILENAME ('LOG', 'users.xml');
DBMS_LOB.createtemporary (l_clob, CACHE => FALSE);
DBMS_LOB.OPEN (l_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile (l_clob, l_bfile, DBMS_LOB.getlength (l_bfile), 1,1);
DBMS_LOB.CLOSE (l_bfile);
l_parser := xmlparser.newparser;
xmlparser.parseclob(l_parser, l_clob);
l_doc := xmlparser.getdocument(l_parser);
xmlparser.freeparser (l_parser);
l_users := xslprocessor.selectnodes(xmldom.makenode(l_doc), '/NamespaceReport/User');
FOR cur_user IN 0 .. xmldom.getlength(l_users) - 1
LOOP
DBMS_OUTPUT.put_line('------------------------------------');
DBMS_OUTPUT.put_line('Process user: ' || cur_user);
l_user := xmldom.item(l_users, cur_user);
l_attrs := xmldom.getattributes(l_user);
FOR cur_attr IN 0 .. xmldom.getlength (l_attrs) - 1
LOOP
l_attr := xmldom.ITEM (l_attrs, cur_attr);
IF xmldom.getnodename (l_attr) = 'email' THEN
l_email := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'phone' THEN
l_phone := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'first_name' THEN
l_first_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'name' THEN
l_name := xmldom.getnodevalue (l_attr);
END IF;
IF xmldom.getnodename (l_attr) = 'last_name' THEN
l_last_name := xmldom.getnodevalue (l_attr);
END IF;
END LOOP;
DBMS_OUTPUT.put_line('l_first_name=' || l_first_name);
DBMS_OUTPUT.put_line('l_last_name=' || l_last_name);
DBMS_OUTPUT.put_line('l_name=' || l_name);
DBMS_OUTPUT.put_line('l_email=' || l_email);
DBMS_OUTPUT.put_line('l_phone=' || l_phone);
l_n := xslprocessor.selectsinglenode(l_user, 'BasicSignon');
l_basic_signon := xmldom.getnodevalue(xmldom.getfirstchild (l_n));
DBMS_OUTPUT.put_line('l_basic_signon=' || l_basic_signon);
l_n := xslprocessor.selectsinglenode(l_user, 'OSSignon');
l_os_signon := xmldom.getnodevalue(xmldom.getfirstchild (l_n));
DBMS_OUTPUT.put_line('l_os_signon=' || l_os_signon);
-- Get a list of all the nodes in the document using the XPATH syntax.
l_nl := xslprocessor.selectnodes(l_user, 'Userclasses/Userclass');
-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_uc IN 0 .. xmldom.getlength(l_nl) - 1
LOOP
l_n := xmldom.item(l_nl, cur_uc);
l_attrs := xmldom.getattributes(l_n);
l_attr := xmldom.item(l_attrs, 0);
l_userclass := xmldom.getnodevalue(l_attr);
DBMS_OUTPUT.put_line('l_userclass=' || l_userclass);
INSERT INTO zbis_users
(basic_signon, os_signon, first_name, last_name,
disp_name, user_email, user_phone, user_class
)
VALUES (l_basic_signon, l_os_signon, l_first_name, l_last_name,
l_name, l_email, l_phone, l_userclass
);
DBMS_OUTPUT.put_line('Record Inserted');
END LOOP;
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
xmldom.freedocument (l_doc);
END;
/
Here's the output:
Code:
tim_hall@db10g> exec zbis_users_load
------------------------------------
Process user: 0
l_first_name=
l_last_name=
l_name=Administrator
[email protected]
l_phone=+99999999
l_basic_signon=Administrator
l_os_signon=admin
l_userclass=Root User Class
Record Inserted
------------------------------------
Process user: 1
l_first_name=name1
l_last_name=name2
l_name=name1 name2
[email protected]
l_phone=888888
l_basic_signon=nameA
l_os_signon=
l_userclass=Others
Record Inserted
l_userclass=Queries
Record Inserted
l_userclass=Report
Record Inserted
PL/SQL procedure successfully completed.
tim_hall@db10g>
I've switched back to using the clob method, but you can convert this as before.
Note. If possible you should switch to the DBMS_ version of all the XML packages. (xmldom -> dbms_xmldom, xslprocessor -> dbms_xslprocessor etc.). The DBMS_ packages are written in C and form part of the kernel so they are faster and more efficient. The packages you are using are the Java versions, which will be slower and produce a bigger drain on the system resources.
For the sake of clarity I've used the same packages as you, but you really should avoid them if possible and switch to the DBMS_ versions. I guess in future releases the java versions will be deprecated.
Ran out of space. Seenext post for conclusion.
-
This is what I would advise:
Code:
CREATE OR REPLACE PROCEDURE zbis_users_load
AS
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.parser;
l_doc dbms_xmldom.domdocument;
l_attrs dbms_xmldom.domnamednodemap;
l_attr dbms_xmldom.domnode;
l_users dbms_xmldom.domnodelist;
l_user dbms_xmldom.domnode;
l_nl dbms_xmldom.domnodelist;
l_n dbms_xmldom.domnode;
l_email zbis_users.user_email%TYPE;
l_phone zbis_users.user_phone%TYPE;
l_first_name zbis_users.first_name%TYPE;
l_name zbis_users.disp_name%TYPE;
l_last_name zbis_users.last_name%TYPE;
l_basic_signon zbis_users.basic_signon%TYPE;
l_os_signon zbis_users.os_signon%TYPE;
l_userclass zbis_users.user_class%TYPE;
BEGIN
l_bfile := BFILENAME ('LOG', 'users.xml');
DBMS_LOB.createtemporary (l_clob, CACHE => FALSE);
DBMS_LOB.OPEN (l_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadfromfile (l_clob, l_bfile, DBMS_LOB.getlength (l_bfile), 1,1);
DBMS_LOB.CLOSE (l_bfile);
l_parser := dbms_xmlparser.newparser;
dbms_xmlparser.parseclob(l_parser, l_clob);
l_doc := dbms_xmlparser.getdocument(l_parser);
dbms_xmlparser.freeparser (l_parser);
l_users := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(l_doc), '/NamespaceReport/User');
FOR cur_user IN 0 .. dbms_xmldom.getlength(l_users) - 1
LOOP
DBMS_OUTPUT.put_line('------------------------------------');
DBMS_OUTPUT.put_line('Process user: ' || cur_user);
l_user := dbms_xmldom.item(l_users, cur_user);
l_attrs := dbms_xmldom.getattributes(l_user);
FOR cur_attr IN 0 .. dbms_xmldom.getlength (l_attrs) - 1
LOOP
l_attr := dbms_xmldom.ITEM (l_attrs, cur_attr);
IF dbms_xmldom.getnodename (l_attr) = 'email' THEN
l_email := dbms_xmldom.getnodevalue (l_attr);
END IF;
IF dbms_xmldom.getnodename (l_attr) = 'phone' THEN
l_phone := dbms_xmldom.getnodevalue (l_attr);
END IF;
IF dbms_xmldom.getnodename (l_attr) = 'first_name' THEN
l_first_name := dbms_xmldom.getnodevalue (l_attr);
END IF;
IF dbms_xmldom.getnodename (l_attr) = 'name' THEN
l_name := dbms_xmldom.getnodevalue (l_attr);
END IF;
IF dbms_xmldom.getnodename (l_attr) = 'last_name' THEN
l_last_name := dbms_xmldom.getnodevalue (l_attr);
END IF;
END LOOP;
DBMS_OUTPUT.put_line('l_first_name=' || l_first_name);
DBMS_OUTPUT.put_line('l_last_name=' || l_last_name);
DBMS_OUTPUT.put_line('l_name=' || l_name);
DBMS_OUTPUT.put_line('l_email=' || l_email);
DBMS_OUTPUT.put_line('l_phone=' || l_phone);
l_n := dbms_xslprocessor.selectsinglenode(l_user, 'BasicSignon');
l_basic_signon := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild (l_n));
DBMS_OUTPUT.put_line('l_basic_signon=' || l_basic_signon);
l_n := dbms_xslprocessor.selectsinglenode(l_user, 'OSSignon');
l_os_signon := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild (l_n));
DBMS_OUTPUT.put_line('l_os_signon=' || l_os_signon);
-- Get a list of all the nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectnodes(l_user, 'Userclasses/Userclass');
-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_uc IN 0 .. dbms_xmldom.getlength(l_nl) - 1
LOOP
l_n := dbms_xmldom.item(l_nl, cur_uc);
l_attrs := dbms_xmldom.getattributes(l_n);
l_attr := dbms_xmldom.item(l_attrs, 0);
l_userclass := dbms_xmldom.getnodevalue(l_attr);
DBMS_OUTPUT.put_line('l_userclass=' || l_userclass);
INSERT INTO zbis_users
(basic_signon, os_signon, first_name, last_name,
disp_name, user_email, user_phone, user_class
)
VALUES (l_basic_signon, l_os_signon, l_first_name, l_last_name,
l_name, l_email, l_phone, l_userclass
);
DBMS_OUTPUT.put_line('Record Inserted');
END LOOP;
END LOOP;
-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freedocument (l_doc);
END;
/
The results are identical in 10g, can't speak for 9i, but they aer supporte on that version also.
Cheers
Tim...
-
thanks a lot. this works perfectly.
i tried help from many forums, but only u made it work for me.
-
Be careful, you'll make my head swell :)
The difficult thing about XML is that there are several ways to approach the same problem. Some people like to manually traverse the tree, some people like to use XPATH to jump directly to the data. In addition there are multiple APIs, TYPEs and frameworks that can be used to get the correct answer. As a result, most people look at XML problems and switch off instantly.
That's why you have to be as precise as possible about your issue and provide the setup scripts. If people have to think too much during the setup they will get bored and move to another forum post.
Good luck and keep reading the documentation.
Cheers
Tim...