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

i am using Oracle 9i.

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...