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  |
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 #425558 is a reply to message #425365] |
Fri, 09 October 2009 11:54 |
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 #425620 is a reply to message #425612] |
Sat, 10 October 2009 07:32 |
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 |
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 #426663 is a reply to message #426633] |
Sun, 18 October 2009 06:27 |
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 12:02:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
