-
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.
-
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
-
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|