DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: read xml to table

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

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

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

  4. #14
    Join Date
    Apr 2005
    Posts
    15
    the script i posted in previous thread reads the xml file. so the API should work. i am using 9i

    regards,
    sudhi

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

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

  7. #17
    Join Date
    Apr 2005
    Posts
    15
    thanks a lot. this works perfectly.

    i tried help from many forums, but only u made it work for me.

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

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