Home » Developer & Programmer » JDeveloper, Java & XML » XML PARSER
XML PARSER [message #358458] Tue, 11 November 2008 07:47 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
i have one procedure which is given below and i given error also can anyone sorted out this.here i want to give the static value to usrid and grpid in table how can i parse that static value for each row.

usrid='USR1'
grpid='GRP1'



create or replace procedure ei_rt
(
userid varchar2,
groupid varchar2,
xmldata CLOB,
errcd  varchar2,
errmsg varchar2
)
as
Begin
Declare
  insctx DBMS_XMLstore.ctxtype;
  rows NUMBER;
  xmldoc CLOB := xmldata;
Begin
  insCtx :=DBMS_XML_Store.newcontext(infusr.ei_rt_summ);
DBMS_XMLStore.clearUpdatecolumnList(insctx);
DBMS_XMLStore.SetupdateColumn(insctx,'VALUEDATE');
DBMS_XMLStore.SetupdateColumn(insctx,'REC_ID');
DBMS_XMLStore.SetupdateColumn(insctx,'TRANSREF');
DBMS_XMLStore.SetupdateColumn(insctx,'DCURR');
DBMS_XMLStore.SetupdateColumn(insctx,'DAMT');

rows := DBMS_XMLStore.insertXML(insCtx,xmlDoc);
DBMS_XMLStore.closeContext(insCtx);
END;
END;


Table Details :
---------------
create table ei_rt_summ (
usrid varchar2(20),
grpid varchar2(20),
recid varchar2(20),
valuedate varchar2(20),
transref  varchar2(50),
dcurr     varchar2(50),
damt      varchar2(50),
ccurr     varchar2(50)
)


Error Log
-----------

Error at line 1:
ORA-31011 : XML Parsing failed
ORA-19202 : Error Occured in XML Processing
LPX-00222 : error received from SAX callback function.
ORA-06512 : at "SYS.DBMS_XMLSTORE", line 78
ORA-06512 : at "INFOUSER.EI_RT_SUMM", line 45
ORA-06512 : at line 5






Re: XML PARSER [message #358606 is a reply to message #358458] Tue, 11 November 2008 23:17 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> create table ei_rt_summ (
  2    usrid	 varchar2(20),
  3    grpid	 varchar2(20),
  4    recid	 varchar2(20),
  5    valuedate varchar2(20),
  6    transref  varchar2(50),
  7    dcurr	 varchar2(50),
  8    damt	 varchar2(50),
  9    ccurr	 varchar2(50)
 10  )
 11  /

Table created.

SCOTT@orcl_11g> create or replace procedure ei_rt
  2  (
  3    userid	 varchar2,
  4    groupid	 varchar2,
  5    xmldata	 CLOB
  6  )
  7  as
  8    insctx	 DBMS_XMLstore.ctxtype;
  9    rows	 NUMBER;
 10    xmldoc	 CLOB := xmldata;
 11  Begin
 12    insCtx := DBMS_XMLStore.newcontext ('EI_RT_SUMM');
 13    DBMS_XMLStore.clearUpdatecolumnList (insctx);
 14    DBMS_XMLStore.SetupdateColumn (insctx, 'USRID');
 15    DBMS_XMLStore.SetupdateColumn (insctx, 'GRPID');
 16    DBMS_XMLStore.SetupdateColumn (insctx, 'VALUEDATE');
 17    DBMS_XMLStore.SetupdateColumn (insctx, 'RECID');
 18    DBMS_XMLStore.SetupdateColumn (insctx, 'TRANSREF');
 19    DBMS_XMLStore.SetupdateColumn (insctx, 'DCURR');
 20    DBMS_XMLStore.SetupdateColumn (insctx, 'DAMT');
 21    xmlDoc := REPLACE (xmlDoc, '<ROW>',
 22  		 '<ROW>
 23  		    <USRID>' || userid || '</USRID>
 24  		    <GRPID>' || groupid || '</GRPID>');
 25    rows := DBMS_XMLStore.insertXML (insCtx, xmlDoc);
 26    DBMS_XMLStore.closeContext (insCtx);
 27  END ei_rt;
 28  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> BEGIN
  2    ei_rt
  3  	 ('USR1',
  4  	  'GRP1',
  5  	  '<ROWSET>
  6  	     <ROW>
  7  	       <VALUEDATE>DATE1</VALUEDATE>
  8  	       <RECID>RECID1</RECID>
  9  	       <TRANSREF>TR1</TRANSREF>
 10  	       <DCURR>DCURR1</DCURR>
 11  	       <DAMT>AMOUNT1</DAMT>
 12  	     </ROW>
 13  	     <ROW>
 14  	       <VALUEDATE>DATE2</VALUEDATE>
 15  	       <RECID>RECID2</RECID>
 16  	       <TRANSREF>TR2</TRANSREF>
 17  	       <DCURR>DCURR2</DCURR>
 18  	       <DAMT>AMOUNT2</DAMT>
 19  	     </ROW>
 20  	   </ROWSET>');
 21  END;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM ei_rt_summ
  2  /

USRID                GRPID                RECID
-------------------- -------------------- --------------------
VALUEDATE            TRANSREF
-------------------- --------------------------------------------------
DCURR
--------------------------------------------------
DAMT
--------------------------------------------------
CCURR
--------------------------------------------------
USR1                 GRP1                 RECID1
DATE1                TR1
DCURR1
AMOUNT1


USR1                 GRP1                 RECID2
DATE2                TR2
DCURR2
AMOUNT2



2 rows selected.

SCOTT@orcl_11g>

Previous Topic: namespace prefix problem
Next Topic: Join in TopLink
Goto Forum:
  


Current Time: Fri Mar 29 06:22:30 CDT 2024