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 Go to next message
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 134 times)
Re: Unable to insert XML Data [message #408627 is a reply to message #408624] Wed, 17 June 2009 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #408641 is a reply to message #408638] Wed, 17 June 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Proceed as I mentioned.

Regards
Michel
Re: Unable to insert XML Data [message #408968 is a reply to message #408624] Thu, 18 June 2009 08:54 Go to previous message
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.
Previous Topic: Trimming Help
Next Topic: Date SQL Query Issue
Goto Forum:
  


Current Time: Thu Dec 08 06:36:24 CST 2016

Total time taken to generate the page: 0.10982 seconds