Home » Developer & Programmer » JDeveloper, Java & XML » Parsing XML document in PL/SQL (Oracle 9i, Windows XP Professional)
Parsing XML document in PL/SQL [message #425110] Wed, 07 October 2009 10:25 Go to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi!
I have the following tables:

SQL> desc COMPANY_INFO1
Name Null? Type
----------------------------------------- -------- ----------------------------
SL_NO NUMBER
COMPANY_NAME VARCHAR2(100)
POSTAL_ADDR POSTALADDR_TY
DIR_PARTNERS_ADDR DIR_PARTNER_ADDR_TY

SQL> desc POSTALADDR_TY
Name Null? Type
----------------------------------------- -------- ----------------------------
D_P_NO NUMBER
MCH_NO NUMBER
LANE_NO NUMBER
STREET VARCHAR2(80)
COLONY VARCHAR2(80)
AREA VARCHAR2(80)
VILLAGE VARCHAR2(80)
MANDAL VARCHAR2(80)
DISTRICT VARCHAR2(80)
STATE VARCHAR2(80)
COUNTRY VARCHAR2(80)
PIN NUMBER

SQL> desc DIR_PARTNER_ADDR_TY
Name Null? Type
----------------------------------------- -------- ----------------------------
DIR_PARTNER1_NAME VARCHAR2(80)
QUALIFICATION QUALIFICATION_TY
PROFILE VARCHAR2(1000)
PAST_EXP VARCHAR2(1000)
CURRENT_POSITION VARCHAR2(50)
ADDRESS VARCHAR2(1000)
PH_SNO NUMBER
DIR_PARTNER2_NAME VARCHAR2(80)

SQL> desc QUALIFICATION_TY
QUALIFICATION_TY VARRAY(3) OF VARCHAR2(50)

Now, I need to insert data in the company_info1 table from an XML file.

I am new to XML. Can you please help me how I can achieve this in a PL/SQL stored procedure?

Also, if I have to change DIR_PARTNERS_ADDR field in the company_info1 table to nested table type instead of object type in order to have in a single record, multiple values for DIR_PARTNER_ADDR column. For example, from a user interface, in some cases, there will be more than one partner or director and in some cases only one partner and/director.

From a friend in ASP.NET, the based on the user selection, XML file would be created and the values in this file need to be inserted into the company_info1 table.

I am struggling a lot to achieve this. Please, help me with example code.

Oracle version : Oracle 9.2.0.1.0
OS: Windows XP Professional

I need this help very urgently.

Thanks,
Moksha
Re: Parsing XML document in PL/SQL [message #425121 is a reply to message #425110] Wed, 07 October 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many examples in "JDeveloper, Java & XML" forum below.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Wed, 07 October 2009 11:17]

Report message to a moderator

Re: Parsing XML document in PL/SQL [message #425363 is a reply to message #425121] Thu, 08 October 2009 13:57 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Sorry, for any message formatting guidelines problems in my earlier message. I will comply with the forum message guidelines going forward.

Oracle version that I am using is: 9.2.0.40 I am trying to look for the functionality as per your suggestions. But, if you can point me to any particular post or with a small example particularly on the second scenario that I described in previous message i.e., when it is not known prior how many values a particular record will have in a column where this column is a nested table, it would really help. I am in very urgent need of help. Will be very thankful for any help.

Thanks,
Moksha


[Updated on: Thu, 08 October 2009 14:05]

Report message to a moderator

Re: Parsing XML document in PL/SQL [message #425365 is a reply to message #425363] Thu, 08 October 2009 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Parsing XML document in PL/SQL [message #425558 is a reply to message #425365] Fri, 09 October 2009 11:54 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel,
The table creation statements are as follows:

CREATE TYPE POSTALADDR_TY AS OBJECT
(D_P_NO  NUMBER,
MCH_NO   NUMBER,
LANE_NO  NUMBER,
STREET   VARCHAR2(80),
COLONY   VARCHAR2(80),
AREA     VARCHAR2(80),
VILLAGE  VARCHAR2(80),
MANDAL   VARCHAR2(80),
DISTRICT VARCHAR2(80),
STATE    VARCHAR2(80),
COUNTRY  VARCHAR2(80),
PIN      NUMBER);
/

CREATE TYPE QUALIFICATION_TY AS VARRAY(3) OF VARCHAR2(50);
/

CREATE TYPE PHNO_TY AS VARRAY(2) OF NUMBER;
/


CREATE OR REPLACE TYPE DIR_PARTNER_ADDR_LIST AS TABLE OF DIR_PARTNER_ADDR_TY;
 /

 CREATE OR REPLACE TYPE COMPANY_INFO_TY AS OBJECT
 (SL_NO NUMBER,
 COMPANY_NAME VARCHAR2(100),
 PH_NO PHNO_TY,
 POSTAL_ADDR POSTALADDR_TY,
 DIR_PARTNERS_ADDR DIR_PARTNER_ADDR_LIST);
 /


CREATE TABLE COMPANY_INFO_TBL OF COMPANY_INFO_TY
OIDINDEX OID_COMPANY_INFO_TBL
NESTED TABLE DIR_PARTNERS_ADDR STORE AS DIR_PARTNER_ADDRS_TY;
/

Insert Statements:

Insert Statement 1:
INSERT INTO COMPANY_INFO_TBL VALUES(1,'PST',
  PHNO_TY(123456),
 POSTALADDR_TY(1,1,NULL,'ST1','COL1','GSTS',NULL,NULL,'DST','HP','IND','342242'),
  DIR_PARTNER_ADDR_LIST(DIR_PARTNER_ADDR_TY(
'DIR1',QUALIFICATION_TY('B.COM'),
NULL,
'DIR OF XYZ COMPANY',
'DIR OF PST',
 'ST.1,RD NO.1',
 34234324,
  NULL)))
/

Insert Statement 2:
INSERT INTO COMPANY_INFO_TBL VALUES(1,'PST',
PHNO_TY(123456),
POSTALADDR_TY(1,1,NULL,'ST1','COL1','GSTS',NULL,NULL,'DST','HP','IND','342242'),
DIR_PARTNER_ADDR_LIST(DIR_PARTNER_ADDR_TY(
'DIR1',QUALIFICATION_TY('B.COM'),
NULL,
'SR.MANAGER',
'SR.MANAGER AT ABC.COM',
 'ST.1,RD NO.1',
 65464231,
NULL),
DIR_PARTNER_ADDR_TY(
'DIR2',QUALIFICATION_TY('M.COM'),
 NULL,
 'VP',
 'VP AT ABC.COM',
 'ST.1,RD NO.1',
 356634643,
 NULL)));

The requirement is that the data would be coming as XML documents either as clob or varchar2 or this file need to be picked up from a directory and this would be input to an Oracle stored procedure. This stored procedure need to read/parse this XML document and using insert statements like above need to store in the COMPANY_INFO_TBL.

Test Case 1: The XML document contains only one director related information ie., the nested table DIR_PARTNER_ADDR_LIST has only one director related information for DIR_PARTNER_ADDR_TY.

Here is the XML:
<?xml version="1.0"?>
<SL_NO>1</SL_NO>       
  <COMPANY_NAME>PST</COMPANY_NAME>   
  <PH_NO>  
   <NUMBER>123456</NUMBER>  
  </PH_NO>      
  <POSTAL_ADDR>    
   <D_P_NO>1</D_P_NO>   
   <MCH_NO>1</MCH_NO> 
   <STREET>ST1</STREET>  
   <COLONY>COL1</COLONY> 
   <AREA>GSTS</AREA>    
   <DISTRICT>DST</DISTRICT>
   <STATE>HP</STATE>   
   <COUNTRY>IND</COUNTRY>
   <PIN>342242</PIN>    
  </POSTAL_ADDR>  
  <DIR_PARTNERS_ADDR>
   <DIR_PARTNER_ADDR_TY>
  <DIR_PARTNER1_NAME>DIR1</DIR_PARTNER1_NAME>
    <QUALIFICATION>
     <VARCHAR2>B.COM</VARCHAR2>
   </QUALIFICATION>
   <PAST_EXP>DIR OF XYZ</PAST_EXP>
   <CURRENT_POSITION>DIR OF PST</CURRENT_POSITION>
   <ADDRESS>ST.1,RD .1</ADDRESS>
    <PH_SNO>34234324</PH_SNO>    
 </DIR_PARTNER_ADDR_TY>     
</DIR_PARTNERS_ADDR>    

The out put would be like the output of the Insert Statement 1 above.

Test Case 2: The XML document contains more than one director related information ie., the nested table DIR_PARTNER_ADDR_LIST has more than one director related information for DIR_PARTNER_ADDR_TY. As an example took directors values, however there can be more than 2 or 3 (it is not known prior)

Attached the XML document COMPANY_INFO_12.txt (since, .xml extension not allowed, attaching with .txt extension) for this.

The out put would be like the output of the Insert Statement 2 above.

******************************************
Hope, I have not written confusingly.

I am in very urgent need of help. Will be very thankful to you for any help.

Note: As per the guidelines, I have used <code></code> tags but they are getting displayed as it is.

Thanks,
Moksha

[Edit MC: it is [code][/code] not <code></code>]

[Updated on: Sat, 10 October 2009 02:55] by Moderator

Report message to a moderator

Re: Parsing XML document in PL/SQL [message #425612 is a reply to message #425558] Sat, 10 October 2009 03:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said there are many examples in this forum.
Here are a couple of them, read them, study them, and come back with your tries or solution.

http://www.orafaq.com/forum/m/419420/102589/#msg_419420
http://www.orafaq.com/forum/m/413699/102589/#msg_413699
http://www.orafaq.com/forum/m/415988/102589/#msg_415988
http://www.orafaq.com/forum/m/424153/102589/#msg_424153

Regards
Michel
Re: Parsing XML document in PL/SQL [message #425620 is a reply to message #425612] Sat, 10 October 2009 07:32 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel,
Thank you for providing these examples. I have tried to apply this and tried as following:

with
data as (
 select xmltype('<?xml version="1.0" ?>
<COMPANY_INFO>
 <SL_NO>1</SL_NO>                                                              
  <COMPANY_NAME>PST</COMPANY_NAME>                                              
  <PH_NO>                                                                       
   <NUMBER>123456</NUMBER>                                                      
  </PH_NO>                                                                      
  <POSTAL_ADDR>                                                                 
   <D_P_NO>1</D_P_NO>                                                           
   <MCH_NO>1</MCH_NO>                                                           
   <STREET>ST1</STREET>                                                         
   <COLONY>COL1</COLONY>                                                        
   <AREA>GSTS</AREA>    
<DISTRICT>DST</DISTRICT>                                                     
   <STATE>HP</STATE>                                                            
   <COUNTRY>IND</COUNTRY>                                                       
   <PIN>342242</PIN>                                                            
  </POSTAL_ADDR>                                                                
  <DIR_PARTNERS_ADDR>                                                           
   <DIR_PARTNER_ADDR_TY>                                                        
    <DIR_PARTNER1_NAME>DIR1</DIR_PARTNER1_NAME>                                 
    <QUALIFICATION>                                                             
     <VARCHAR2>B.COM</VARCHAR2>                                                 
    </QUALIFICATION>   
<PAST_EXP>SR.MANAGER</PAST_EXP>                                             
    <CURRENT_POSITION>SR.MANAGER AT ABC.COM</CURRENT_POSITION>                  
    <ADDRESS>ST.1,RD NO.1</ADDRESS>                                             
    <PH_SNO>65464231</PH_SNO>                                                   
   </DIR_PARTNER_ADDR_TY>                                                       
   <DIR_PARTNER_ADDR_TY>                                                        
    <DIR_PARTNER1_NAME>DIR2</DIR_PARTNER1_NAME>                                 
    <QUALIFICATION>                                                             
     <VARCHAR2>M.COM</VARCHAR2>                                                 
    </QUALIFICATION>                                                            
    <PAST_EXP>VP</PAST_EXP> 
<CURRENT_POSITION>VP AT ABC.COM</CURRENT_POSITION>                          
    <ADDRESS>ST.1,RD NO.1</ADDRESS>                                             
    <PH_SNO>356634643</PH_SNO>                                                  
   </DIR_PARTNER_ADDR_TY>                                                       
  </DIR_PARTNERS_ADDR>
</COMPANY_INFO>') val
from dual
)
select extractvalue(value(t),'/SL_NO') slno,
extractvalue(value(t1),'/COMPANY_NAME ') COMPANY_NAME,
extractvalue(value(t2),'/NUMBER') PH_NO,
extractvalue(value(t3),'/DIR_PARTNER_ADDR_TY/DIR_PARTNER1_NAME') DIRECTOR1
from data,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/SL_NO'))) t,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/COMPANY_NAME '))) t1,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/PH_NO/NUMBER '))) t2,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/DIR_PARTNERS_ADDR /DIR_PARTNER_ADDR_TY '))) t3
/

The output is:
SLNO
--------------------------------------------------------------------------------
COMPANY_NAME
--------------------------------------------------------------------------------
PH_NO
--------------------------------------------------------------------------------
DIRECTOR1
--------------------------------------------------------------------------------
1
PST
123456
DIR1


SLNO
--------------------------------------------------------------------------------
COMPANY_NAME
--------------------------------------------------------------------------------
PH_NO
--------------------------------------------------------------------------------
DIRECTOR1
--------------------------------------------------------------------------------
1
PST
123456
DIR2


But, I am not sure how to store these values in variables with DIR1 related information as one table object and DIR2 as another object and then insert into the values into the nested table. However, to have DIR1 related information together and DIR2 information together, I have tried as follows:
with
data as (
 select xmltype('<?xml version="1.0" ?>
<COMPANY_INFO>
 <SL_NO>1</SL_NO>                                                              
  <COMPANY_NAME>PST</COMPANY_NAME>                                              
  <PH_NO>                                                                       
   <NUMBER>123456</NUMBER>                                                      
  </PH_NO>                                                                      
  <POSTAL_ADDR>                                                                 
   <D_P_NO>1</D_P_NO>                                                           
   <MCH_NO>1</MCH_NO>                                                           
   <STREET>ST1</STREET>                                                         
   <COLONY>COL1</COLONY>                                                        
   <AREA>GSTS</AREA>    
<DISTRICT>DST</DISTRICT>                                                     
   <STATE>HP</STATE>                                                            
   <COUNTRY>IND</COUNTRY>                                                       
   <PIN>342242</PIN>                                                            
  </POSTAL_ADDR>                                                                
  <DIR_PARTNERS_ADDR>                                                           
   <DIR_PARTNER_ADDR_TY name="DIR1" qual1="B.COM" qual2="" qual3="" pastexp="Worked as Sr.Manager at ABC.COM, has 15+ years of managerial experience" curpos="MD"  addr=" ST.1,RD NO.1" phsno="65464231" >                                                        
    </DIR_PARTNER_ADDR_TY>                                                       
   <DIR_PARTNER_ADDR_TY name="DIR2" qual1="M.COM" qual2="B.COM" qual3="" pastexp="Worked as Sr.Manager at ABC.COM, has 15+ years of managerial experience" curpos="MD"  addr=" ST.1,RD NO.1" phsno="65464231" >                                                            
   </DIR_PARTNER_ADDR_TY>                                                       
  </DIR_PARTNERS_ADDR>
</COMPANY_INFO>') val
from dual
)
select extractvalue(value(t),'/SL_NO') slno,
extractvalue(value(t1),'/COMPANY_NAME ') COMPANY_NAME,
extractvalue(value(t2),'/NUMBER') PH_NO,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@name') name,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@qual1') qual1,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@qual2') qual2,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@qual3') qual3,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@pastexp') pastexperience,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@curpos') currentposition,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@addr') address,
extractvalue(value(b),'/DIR_PARTNER_ADDR_TY/@phsno') phsno
from data, 
table(xmlsequence(extract(data.val, '/COMPANY_INFO/SL_NO'))) t,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/COMPANY_NAME '))) t1,
table(xmlsequence(extract(data.val, '/COMPANY_INFO/PH_NO/NUMBER '))) t2,
table(xmlsequence(extract(data.val,'/COMPANY_INFO/DIR_PARTNERS_ADDR/ DIR_PARTNER_ADDR_TY'))) b
/

The output is that it is continuously going on scrolling and not getting stopped. At last I have used the Cancel option from SQL*PLUS.


If I am not asking any silly question or missing anything here, plesae do not mind.

But, please provide me a pointer to a post having a stored procedure storing information into a nested table which further contains varray's and objects or please help me with an example.

I am in urgent need of this help. Will be very thankful to you for any help.

Thanks,
Moksha.
Re: Parsing XML document in PL/SQL [message #426621 is a reply to message #425110] Sat, 17 October 2009 05:37 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel,
Thank you very much once again and with the examples given I am able to extract the XML nodes. Here is the solution to extract unknown number of nodes:
CREATE or REPLACE PROCEDURE loadCompanyInfo5 (coname IN varchar2, clobxml IN clob) IS
  /*Note: First agrument to this procedure is company name and rest is coming as clob*/

    dnopno number;
    ciXML XMLType; 

BEGIN   
  -- Store the Purchase Order XML in the CLOB variable
  ciXML := XMLTYPE(clobxml);
select extractvalue(ciXML,'//companyinfo/postaladdr/dpno') into dnopno from dual;
for i in ( select extract(column_value,'//name/text()').getstringval() dir_name, 

extract(column_value,'//qualification') qual_nodes,
extract(column_value,'//profile/text()').getstringval() dir_profile,
extract(column_value,'//pastexp/text()').getstringval() dir_pastexp,
extract(column_value,'//currentpos/text()').getstringval() dir_currentpos,
extract(column_value,'//address/text()').getstringval()dir_address, 

extract(column_value,'//phsno/text()').getstringval() dir_phsno
 from table( xmlsequence( extract( ciXML, '//companyinfo/Dirpar' ))) ) loop
                  dbms_output.put_line('Director Name: ' || i.dir_name);

                  -- get each days values
                  for j in ( select 
                        extract(column_value,'//qual1/text()').getstringval() dir_qual1, 
                        extract(column_value,'//qual2/text()').getstringval() dir_qual2, 
                        extract(column_value,'//qual3/text()').getstringval() dir_qual3 
                  from table( xmlsequence( extract( i.qual_nodes, '*' ))) ) loop

-- populate temporary table
insert into DIR_PARTNER_ADDR_LIST_TEMP values(i.dir_name, QUALIFICATION_TY(j.dir_qual1, 
j.dir_qual2, j.dir_qual3),i.dir_profile, i.dir_pastexp, i.dir_currentpos, i.dir_address, 
i.dir_phsno);
end loop;
end loop;

commit;
--Handle the exceptions
EXCEPTION
  WHEN OTHERS THEN 
    raise_application_error(-20101, 'Exception occurred in loadCompanyInfo procedure 

:'||SQLERRM); 
END loadCompanyInfo5 ;



Now, I am facing one problem, I am not sure how to insert into the table: COMPANY_INFO_TBL (structure of which is as in the previous messages).

If I have the insert statement for this table in the above loops, then it will insert multiple rows. So, I am not getting a way as how to insert only one record as the Directors information is related to this one company only and this is a nested table containing varrays also.

Please, help me. If it is not possible having the nested table & varrays is there any other way i.e., instead of nested table, I should be having the table columns as XMLTYPE and then retrieve the information from this? If I have to use XMLTYPE, then can you help me with an example as how can I query the table with company name & City & State values in the postal address tags?

Also, in a similar way, if I have 2 more clob columns in the stored procedure as IN parameters, which are again need to be stored as the above clob file into the table where the fields contain varrays and objet types, please advise me how can I achieve insertion into the table as one record inserting the data from all these input IN clob parameters.

Thank you
Moksha

[Updated on: Sat, 17 October 2009 09:23] by Moderator

Report message to a moderator

Re: Parsing XML document in PL/SQL [message #426633 is a reply to message #426621] Sat, 17 October 2009 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I do not use stored varrays or nested tables there are not relational and perform very poorly. Oracle only implements them because some customers asked it.
Use master/details tables.
Varrays and tables are implemented with master/details tables in the end but with many overhead to show them as varrays and nested tables.
So use a pure relational model (each field is atomic) and don't use these kinds of stuff.

Regards
Michel
Re: Parsing XML document in PL/SQL [message #426663 is a reply to message #426633] Sun, 18 October 2009 06:27 Go to previous messageGo to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel!
Thank you very much. I definitely now not go for this object relational concepts instead would follow your advise. However, curious to know one small clarification, whether using XMLTYPE of columns is advised or not and under what cirumstances it is advised to use XMLTYPE. Can you please clarify and advise?

Thanks,
Moksha.
Re: Parsing XML document in PL/SQL [message #426666 is a reply to message #426663] Sun, 18 October 2009 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the same, XML is good to communicate between applications.
But if you have to work with its elements inside an application it should be better to use a storage the application well manipulates.
In the case of Oracle, if you have to read/write/modify some elements of XML inside the database (that is using SQL or PL/SQL) it should be better to store them in a relational form: tables and solumns.
Now if Oracle is just a container of some results you want to exchange between applications outside Oracle, it is good to use XML.
Just my opinion.

Regards
Michel
Re: Parsing XML document in PL/SQL [message #426668 is a reply to message #426666] Sun, 18 October 2009 12:40 Go to previous message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi Michel,
Thank you very much for sharing your opinion and helping to solve my problem.

Thanks,
Moksha
Previous Topic: help in extracting xml
Next Topic: Where i should write Java Code?
Goto Forum:
  


Current Time: Thu Mar 28 06:13:26 CDT 2024