Working with XMLType and xmltable

Query to get multiple records from a single XML.

select * from xmltable('*' passing xmltype('<employee>
<emp>

    <empno>102</empno>

    <ename>Krishna</ename>

    <job>Accounts</job>

    <mgr></mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

<emp>

    <empno>101</empno>

    <ename>Raja</ename>

    <job>Development</job>

    <mgr>102</mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

</employee>').extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

                                     

Procedure to insert multiple records from an XML into a table:        

CREATE OR REPLACE PROCEDURE scott.d_loadxmldata (i_data xmltype)

AS

BEGIN

INSERT INTO emp_load

select * from xmltable('*' passing i_data.extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

commit;

END;

How to convert a oracle table data into XML

SQL> SELECT DBMS_XMLGEN.GETXML('SELECT * FROM EMP') TEST_XML FROM DUAL;