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