Home » Developer & Programmer » JDeveloper, Java & XML » DBMS_XMLSTORE not able to insert values for nested tags (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi)
DBMS_XMLSTORE not able to insert values for nested tags [message #471360] Fri, 13 August 2010 10:59 Go to next message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

I am using anonymous block to insert values from .XML document to emp table.
Its inserting row into EMP table but not inserting values for nested tags(DEPNM,and DEPNO) .
Any idea whats wrong with the code

DECLARE
  insCtx DBMS_XMLSTORE.ctxType;
  rows NUMBER;
  XMLDOC CLOB ;
  VSFILE UTL_FILE.FILE_TYPE;
   VNEWLINE VARCHAR2(2000);
   TEXT     CLOB;--long(4000):= '' ;

BEGIN

        XMLDOC := '<rowset>
       <ROW>
         <EMPNO>920</EMPNO>
         <SAL>1800</SAL>
         <DEPTNO>30</DEPTNO>
         <DEP>
            <DEPNO>10</DEPNO>
            <DEPNM>aaa</DEPNM>
          </DEP>
         <HIREDATE>17-DEC-2002</HIREDATE>
         <JOB>ST_CLERK</JOB>
       </ROW>
       <ROW>
         <EMPNO>921</EMPNO>
         <SAL>2000</SAL>
         <DEPTNO>30</DEPTNO>
         <HIREDATE>31-DEC-2004</HIREDATE>
         <JOB>ST_CLERK</JOB>
       </ROW>
</rowset>';        

  insCtx := DBMS_XMLSTORE.newContext('EMP'); -- Get saved context
  DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(INSCTX); -- Clear the update settings
  --DBMS_XMLSTORE.SETROWTAG(INSCTX,'RW');---- set tag

	/*Set the columns to be updated as a list of values  can be ignore 
	and The default is to insert values for all the columns whose corresponding 
	elements are present in the XML document.*/
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'EMPNO'); 
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'HIREDATE');
  DBMS_XMLSTORE.setUpdateColumn(insCtx, 'SAL');
  DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPTNO'); 
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'JOB');
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'DEPNO');
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'DEPNM');

  -- Insert the doc. 
  rows := DBMS_XMLSTORE.insertXML(insCtx, xmlDoc);
  DBMS_OUTPUT.put_line(rows || ' rows inserted.');

  -- Close the context
  DBMS_XMLSTORE.CLOSECONTEXT(INSCTX); 
END;

[Updated on: Fri, 13 August 2010 11:12]

Report message to a moderator

Re: DBMS_XMLSTORE not able to insert values for nested tags [message #475293 is a reply to message #471360] Tue, 14 September 2010 04:47 Go to previous message
ashishmate
Messages: 90
Registered: February 2005
Location: Mumbai
Member

heres the answer... you will have to wright XSL first which will read nested tags in you XML.


DECLARE
  insCtx DBMS_XMLSTORE.ctxType;
  rows NUMBER;
  XMLDOC CLOB ;
  VSFILE UTL_FILE.FILE_TYPE;
   VNEWLINE VARCHAR2(2000);
   TEXT     CLOB;--long(4000):= '' ;
  -- Place the XML from above, in quotes in the XMLType() call
  v_xml XMLType := XMLType( '<ROWSET>
      <ROW>
         <EMPNO>920</EMPNO>
         <SAL COMM="100" JOB="ashish">1800</SAL>
         <DEP><DEPNO>10</DEPNO></DEP>
         <DEPTNO>30</DEPTNO>
         <HIREDATE>17-DEC-2002</HIREDATE>
         <JOB>ST_CLERK</JOB>
         </ROW>
      <ROW>
         <EMPNO>111</EMPNO>
         <SAL COMM="999" JOB="123468">9999</SAL>
         <DEP><DEPNO>56</DEPNO></DEP>
         <DEPTNO>79</DEPTNO>
         <HIREDATE>17-DEC-1999</HIREDATE>
         <JOB>ST_CdxxLERK</JOB>
         </ROW>
         </ROWSET>' );

  -- XLS is requroed if you are XML got nesrtes tags in it 
  v_xsl XMLType := XMLType( '<?xml version="1.0"?>
<xsl:stylesheet 
               xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
               version="1.0">
  <xsl:template match="/">
    <ROWSET>
<xsl:for-each select="ROWSET/ROW">
      <ROW>
        <EMPNO>
          <xsl:value-of select="EMPNO" />
        </EMPNO>
        <SAL>
          <xsl:value-of select="SAL" />
        </SAL>
        <COMM> ----- nested tabg 
          <xsl:value-of select="SAL/@COMM" />
        </COMM>
        <JOB>  ----- nested tabg 
          <xsl:value-of select="SAL/@JOB" />
        </JOB>
        <DEPTNO>
          <xsl:value-of select="DEPTNO" />
        </DEPTNO>        
        <DEPNO>
          <xsl:value-of select="DEP/DEPNO" />
        </DEPNO>
      </ROW>
</xsl:for-each>      
    </ROWSET>
  </xsl:template>
</xsl:stylesheet>
' );
BEGIN  
  insCtx := DBMS_XMLSTORE.newContext('EMPXML'); -- Get saved context
  DBMS_XMLSTORE.CLEARUPDATECOLUMNLIST(INSCTX); -- Clear the update settings
  -- Insert the doc. 
    DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'EMPNO'); 
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'HIREDATE');
  DBMS_XMLSTORE.setUpdateColumn(insCtx, 'SAL');
  DBMS_XMLSTORE.setUpdateColumn(insCtx, 'DEPTNO'); 
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'JOB');
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'DEPNO');
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'DEPNM');
  DBMS_XMLSTORE.SETUPDATECOLUMN(INSCTX, 'COMM');

  rows := DBMS_XMLSTORE.insertXML(insCtx, XMLType.transform(v_xml, v_xsl));
  DBMS_OUTPUT.put_line(rows || ' rows inserted.');

  -- Close the context
  DBMS_XMLSTORE.CLOSECONTEXT(INSCTX); 
COMMIT;
END;
Previous Topic: Varchar2 to XML conversion
Next Topic: PL/SQL - EXTRACTVALUE + xmltype + DBMS_XMLGEN
Goto Forum:
  


Current Time: Thu Mar 28 09:41:27 CDT 2024