Home » SQL & PL/SQL » SQL & PL/SQL » Unable to insert XML Data (ORACLE 9i)
( ) 1 Vote
Unable to insert XML Data [message #408624] |
Wed, 17 June 2009 01:52  |
sumithaswar
Messages: 6 Registered: June 2009
|
Junior Member |
|
|
Hi,
I want to insert xml data into a table (crmk_kplusdata).
The procedure executes with no errors , but no data is inserted in the table ..
i am attaching the xml file.
my procedure is as follows..
create or replace procedure CRM_UploadKPLUS
(
v_xmlstr IN CLOB ,
V_RETURNMSG OUT VARCHAR2
)
AS
Details XMLTYPE;
BEGIN
Details := xmltype.createxml(v_xmlstr);
delete from crmk_kplusdata;
for j IN
(
SELECT XMLTYPE.EXTRACT(VALUE (a), '/Table/Location/text()').getstringval() AS v_str1,
XMLTYPE.EXTRACT(VALUE (a), '/Table/CountriesGrp_Id/text()').getstringval() AS v_str2,
XMLTYPE.EXTRACT(VALUE (a), '/Table/CountriesGrp_ShortName/text()').getstringval() AS v_str3,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Countries_ShortName/text()').getstringval() AS v_str4,
XMLTYPE.EXTRACT(VALUE (a), '/Table/LimitsTypes_ShortName/text()').getstringval() AS v_str5,
XMLTYPE.EXTRACT(VALUE (a), '/Table/DealsGroup/text()').getstringval() AS v_str6,
XMLTYPE.EXTRACT(VALUE (a), '/Table/KdbTables_DisplayName/text()').getstringval() AS v_str7,
XMLTYPE.EXTRACT(VALUE (a), '/Table/DealId/text()').getstringval() AS v_str8,
XMLTYPE.EXTRACT(VALUE (a), '/Table/TradeDate/text()').getstringval() AS v_str9,
XMLTYPE.EXTRACT(VALUE (a), '/Table/MaturityDate/text()').getstringval() AS v_str10,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Kind/text()').getstringval() AS v_str11,
XMLTYPE.EXTRACT(VALUE (a), '/Table/C_Cpty_ShortName/text()').getstringval() AS v_str12,
XMLTYPE.EXTRACT(VALUE (a), '/Table/I_Cpty_ShortName/text()').getstringval() AS v_str13,
XMLTYPE.EXTRACT(VALUE (a), '/Table/FoldersGrp_ShortName/text()').getstringval() AS v_str14,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Currencies_ShortName/text()').getstringval() AS v_str15,
XMLTYPE.EXTRACT(VALUE (a), '/Table/F_Folders_ShortName/text()').getstringval() AS v_str16,
XMLTYPE.EXTRACT(VALUE (a), '/Table/T_TypeOfInstr_ShortName/text()').getstringval() AS v_str17,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Cu_Currencies_ShortName/text()').getstringval() AS v_str18,
XMLTYPE.EXTRACT(VALUE (a), '/Table/D_Amount/text()').getstringval() AS v_str19,
XMLTYPE.EXTRACT(VALUE (a), '/Table/D_Market/text()').getstringval() AS v_str20,
XMLTYPE.EXTRACT(VALUE (a), '/Table/D_Sensitivity/text()').getstringval() AS v_str21,
XMLTYPE.EXTRACT(VALUE (a), '/Table/L_Amount/text()').getstringval() AS v_str22,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Column1/text()').getstringval() AS v_str23,
XMLTYPE.EXTRACT(VALUE (a), '/Table/D_IssueToMat/text()').getstringval() AS v_str24,
XMLTYPE.EXTRACT(VALUE (a), '/Table/D_SettlToMat/text()').getstringval() AS v_str25,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Column2/text()').getstringval() AS v_str26,
XMLTYPE.EXTRACT(VALUE (a), '/Table/KdbTables_Id/text()').getstringval() AS v_str27,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Cpty_Id/text()').getstringval() AS v_str28,
XMLTYPE.EXTRACT(VALUE (a), '/Table/CptyType/text()').getstringval() AS v_str29,
XMLTYPE.EXTRACT(VALUE (a), '/Table/LegalStatus/text()').getstringval() AS v_str30,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Countries_ShortName1/text()').getstringval() AS v_str31,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Column3/text()').getstringval() AS v_str32,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Column4/text()').getstringval() AS v_str33,
XMLTYPE.EXTRACT(VALUE (a), '/Table/Column5/text()').getstringval() AS v_str34
FROM TABLE
(XMLSEQUENCE(Details.EXTRACT('/NewDataSet/Table'))
)a
)
LOOP
insert into crmk_kplusdata
(location, countriesgrp_id, countriesgrp_shortname, riskcountry,
limitstypes_shortname, dealsgroup, kdbtables_displayname,
dealid, tradedate, maturitydate, kind, c_cpty_shortname,
i_cpty_shortname, foldersgrp_shortname, lt_currencies_shortname,
f_folders_shortname, t_typeofinstr_shortname, cu_currencies_shortname,
d_amount, d_market, d_sensitivity, l_amount, exposure, d_issuetomat,
d_settltomat, limitsid, tableid, c_cpty_id, cptytype, legalstatus,
parentcountry, ratingriskcountry, ratingparentcountry)
values
(
j.v_str1,j.v_str2,j.v_str3,j.v_str4,
j.v_str5,j.v_str6,j.v_str7,
j.v_str8,j.v_str9,j.v_str10,j.v_str11,j.v_str12,
j.v_str13,j.v_str14,j.v_str15,
j.v_str16,j.v_str17,j.v_str18,
j.v_str19,j.v_str20,j.v_str21,j.v_str22,j.v_str23,j.v_str24,j.v_str25,j.v_str26,
j.v_str27,j.v_str28,j.v_str29,j.v_str30,j.v_str31,
j.v_str32,j.v_str33) ;
END LOOP;
commit ;
V_RETURNMSG := 'Data Inserted Successfully in KPLUS DATA';
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
V_RETURNMSG := 'Error in Inserting KPLUS DATA '|| SQLERRM;
END ;
-
Attachment: crmplus.xml
(Size: 423.15KB, Downloaded 812 times)
|
|
|
Re: Unable to insert XML Data [message #408627 is a reply to message #408624] |
Wed, 17 June 2009 02:14   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
From your previous post:
Michel Cadot wrote on Tue, 16 June 2009 13:35 | ...
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.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
Regards
Michel
|
And you said:
sumithaswar wrote on Wed, 17 June 2009 08:40 | thanks for the reply.
my apologies for not following the guidelines, will take care from onwards...
|
And as for this post you didn't follow what you say.
Can we trust you?
Regards
Michel
[Updated on: Wed, 17 June 2009 02:14] Report message to a moderator
|
|
|
Re: Unable to insert XML Data [message #408638 is a reply to message #408627] |
Wed, 17 June 2009 03:14   |
sumithaswar
Messages: 6 Registered: June 2009
|
Junior Member |
|
|
Dear Michel,
I have formatted the code in PL/SQL formatter.the same output i have pasted in the post..
as for the length of the code , i dont have any option other than to paste the whole procedure.
please guide..
|
|
|
|
Re: Unable to insert XML Data [message #408968 is a reply to message #408624] |
Thu, 18 June 2009 08:54  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Remove that Exception handler.
2) Add a count that starts at 0 and increments by one every time you do an insert, and include that count in the return value - I'm willing to bet it will be 0
3) Actually show us what the sample data you pass in is - the problem is almost certainly that the data you pass in doesn't match thepath you provide for the extract.
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:31:44 CST 2025
|