3 Replies Latest reply: Aug 24, 2012 4:58 AM by user9275705 RSS

    How to read XML attribute value using DBMS_XMLSTORE

    Venkatachalam Periyasamy
      the following xml data

      +<ROWSET>+
      +<ROW>+
      +<EMPNO>2290</EMPNO>+
      +<SAL>2000</SAL>+
      +<HIREDATE>31-DEC-1992</HIREDATE>+
      +<TYP>+
      +<ENO>123456</ENO>+
      +<ENAME>attr_name</ENAME>+
      +</TYP>+
      +</ROW>+
      +</ROWSET>+

      The above XML data has stored in below object table with the help of DBMS_XMLSTORE

      CREATE OR REPLACE TYPE typ_dummy AS OBJECT
      (
      eno NUMBER,
      ename VARCHAR2(100)
      );

      CREATE TABLE EMP
      (
      empno VARCHAR2(25),
      sal NUMBER,
      hiredate DATE,
      typ typ_dummy
      );


      DECLARE
      insCtx DBMS_XMLStore.ctxType;
      rows NUMBER;
      xmldoc CLOB :=
      '<ROWSET>
      <ROW num="1">
      <SAL>1800</SAL>
      <EMPNO>7369</EMPNO>
      <HIREDATE>27-AUG-1996</HIREDATE>
      </ROW>
      <ROW>
      <EMPNO>2290</EMPNO>
      <SAL>2000</SAL>
      <HIREDATE>31-DEC-1992</HIREDATE>
      <TYP ENO="123456" ENAME="attr_name"/>
      <TYP>
      <ENO>123456</ENO>
      <ENAME>attr_name</ENAME>
      </TYP>
      </ROW>
      </ROWSET>';
      BEGIN
      insCtx := DBMS_XMLStore.newContext('emp'); -- get saved context
      rows := DBMS_XMLStore.insertXML(insCtx, xmlDoc);
      DBMS_XMLStore.closeContext(insCtx);
      END;



      but I dont know if the xml contain attribute values for particular node means how to insert into the table (suppose how to create the table structure)


      +<ROWSET>+
      +<ROW>+
      +<EMPNO>2290</EMPNO>+
      +<SAL>2000</SAL>+
      +<HIREDATE>31-DEC-1992</HIREDATE>+
      *+<TYP ENO="123456"  ENAME="attr_name" />+*
      +</TYP>+
      +</ROW>+
      +</ROWSET>+
        • 1. Re: How to read XML attribute value using DBMS_XMLSTORE
          odie_63
          You can declare the object type like this :
          CREATE OR REPLACE TYPE typ_dummy AS OBJECT (
            "@ENO"   NUMBER
          , "@ENAME" VARCHAR2(100)
          );
          /
          Oracle will know that the XML attributes are to be mapped to the object attributes.

          But personally, I wouldn't use DBMS_XMLSTORE :
          INSERT INTO emp (empno, sal, hiredate, typ, eno, ename)
          SELECT empno, sal, hiredate, eno, ename
          FROM XMLTable('/ROWSET/ROW'
                 passing xmltype(xmlDoc)
                 columns empno    varchar2(25)  path 'EMPNO'
                       , sal      number        path 'SAL'
                       , hiredate date          path 'HIREDATE'
                       , eno      number        path 'TYP/@ENO'
                       , ename    varchar2(100) path 'TYP/@ENAME'
               )
          ;
          • 2. Re: How to read XML attribute value using DBMS_XMLSTORE
            Venkatachalam Periyasamy
            Thanks for your reply. :)

            Its working fine in the DBMS_XMLSTORE package. Here I have given the code
            CREATE OR REPLACE TYPE typ_dummy AS  OBJECT
            ( "@ENO"   NUMBER, 
              "@ENAME" VARCHAR2(100),
              eno      NUMBER,
              ename    VARCHAR2(100));
            /
            
            CREATE TABLE EMP
            (
              empno    VARCHAR2(25),
              sal      NUMBER,
              hiredate DATE,
              typ      TYP_DUMMY
            );
            /
            DECLARE
              insCtx DBMS_XMLStore.ctxType;
              rows NUMBER;
              xmldoc CLOB :=
                '<ROWSET>
                   <ROW num="1">
                     <SAL>1800</SAL>
                     <EMPNO>739</EMPNO>
                     <HIREDATE>27-AUG-1996</HIREDATE>
                       <TYP ENO="739" ENAME="Nazurullah">
                       <ENO> 1 </ENO>
                       <ENAME> ALDRIN </ENAME>
                       </TYP>
                   </ROW>
                   <ROW>
                     <SAL>18000</SAL>
                     <EMPNO>7369</EMPNO>
                     <HIREDATE>27-AUG-1996</HIREDATE>
                     <TYP ENO="7369" ENAME="PEPPIN" />
                   </ROW>
                   <ROW>
                     <SAL>37000</SAL>
                     <EMPNO>20701</EMPNO>
                     <HIREDATE>27-AUG-1996</HIREDATE>
                     <TYP>
                       <ENO> 20701 </ENO>
                       <ENAME> VENKATACHALAM </ENAME>
                     </TYP>
                   </ROW>
                 </ROWSET>';
            BEGIN
              insCtx := DBMS_XMLStore.newContext('emp'); -- get saved context
              -- Now insert the doc.
              -- This will only insert into EMPNO, SAL and HIREDATE columns
              rows := DBMS_XMLStore.insertXML(insCtx, xmlDoc);
              -- Close the context
              DBMS_XMLStore.closeContext(insCtx); 
            END;
            SELECT * FROM emp;
            
            
            EMPNO        SAL HIREDATE          TYP(@ENO, @ENAME, ENO, ENAME) 
            ----- ---------- --------       -------------------------------------------------------------
            739         1800 27-AUG-96     TYP_DUMMY(739, 'Nazurullah', 1, ' ALDRIN ')
            
            7369       18000 27-AUG-96     TYP_DUMMY(7369, 'PEPPIN', NULL, NULL)
            
            20701      37000 27-AUG-96     TYP_DUMMY(NULL, NULL, 20701, ' VENKATACHALAM ')
            • 3. Re: How to read XML attribute value using DBMS_XMLSTORE
              user9275705
              How to mention the column names before using the comment "DBMS_XMLStore.insertXML".

              For Normal columns we can use the comment "DBMS_XMLSTORE.setUpdateColumn(insCtx, 'ENO');" to mention the column names. How to do the same for Object Types ?