read xml to table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: read xml to table

  1. #1
    Join Date
    Apr 2005
    Posts
    15

    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="test@domain.com" 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
    Last edited by spicysudhi; 06-24-2005 at 06:16 AM.

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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=test@domain.com
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Apr 2005
    Posts
    15
    many many thanks.

    regards,
    Sudhi

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Apr 2005
    Posts
    15
    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="SCM@domain.com" 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

  6. #6
    Join Date
    Apr 2005
    Posts
    15
    i am using Oracle 9i.

  7. #7
    Join Date
    Apr 2005
    Posts
    15
    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

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    l_email=SCM@domain.com
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Apr 2005
    Posts
    15
    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

  10. #10
    Join Date
    Apr 2005
    Posts
    15
    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="email1@domain.com" phone="+99999999" first_name="" name="Administrator" last_name="">
      < BasicSignon>Administrator< /BasicSignon> 
      < OSSignon>admin< /OSSignon> 
    < Userclasses>
      < Userclass name="Root User Class" /> 
      < /Userclasses>
      < /User>
    < User email="email2@domain.com" 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width