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

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

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="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

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

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="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