CODEWITHSUNDEEP
sqlxmloracle-databaseplsqlxmltable
laasya
laasya

Reputation: 5

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

Upvotes: 0

Views: 330

Answers (1)

MT0
MT0

Reputation: 168137

  • You don't need PL/SQL and can do it all in a single SQL statement.
  • You need to use path '/properties[@Name="firstname"]' as you want the attribute called Name with the value firstname rather than searching for the existence of an attribute called firstname.

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

Upvotes: 1

Related Questions