sqlxmloracle-databaseplsqlxmltable

Stored procedure to Convert Clob field of table into xmltype and split xml values to store in the table


I have a table with Clob field

Create table xml_data
(id number,employee_data clob);

Employee_data table xml :
<employees>
<employee id=1>
<properties Name="firstname">Sherlock</properties>
<properties Name="lastname">Holmes</properties>
<properties Name="age">30</properties>
<properties Name="department" >investigation </properties>
</employee>

<employee id=2>
<properties Name="firstname">John</properties>
<properties Name="lastname">Watson</properties>
<properties Name="age">30</properties>
<properties Name="department">writing </properties>
</employee>
</employees>

Create table employees
(firstname varchar2(10),
lastname varchar2(10),
age number) ;

here is my code:

declare 
v_xml xmltype;
begin 
select xmltype(x.employee_data) into v_xml from xml_data x where id=1;
insert into employees (firstname,lastname,age) 
select firstname,lastname,age from xmltable('/employees/employee[@id=1]')
       passing v_xml
       columns firstname varchar2(30) path '/properties[@firstname]',
               lastname varchar2(30) path '/properties[@lastname]',
               age number path '/properties[@age]');
end;
/

I am excepting the following output:

employees table:

firstname lastname age
sherlock holmes 30

but no value is getting inserted into the employees table.

Can anybody please suggest a better approach to this problem


Solution

  • For example:

    insert into employees (firstname,lastname,age)
    select firstname,
           lastname,
           age
    from   xml_data d
           CROSS JOIN xmltable(
             '/employees/employee[@id=1]'
             passing XMLTYPE(d.employee_data)
             columns
               firstname varchar2(30) path 'properties[@Name="firstname"]',
               lastname  varchar2(30) path 'properties[@Name="lastname"]',
               age       number       path 'properties[@Name="age"]'
           )
    WHERE  d.id = 1;
    

    Also, your data is invalid as you need to quote the values in the XML element attributes (rather than using HTML syntax which is more relaxed about that) so <employee id=1> should be <employee id="1">.

    db<>fiddle here