-
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
l_email=email1@domain.com
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
l_email=email2@domain.com
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.
Last edited by TimHall; 06-30-2005 at 05:26 AM.
-
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|