This discussion is archived
3 Replies Latest reply: Aug 24, 2012 2:58 AM by user9275705 RSS

How to read XML attribute value using DBMS_XMLSTORE

Venkatachalam Periyasamy Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points