I have an XML containing relation data such as this
<object name="object1">
<attribute name="attribute1"/>
<attribute name="attribute2">
<value>value 1</value>
<value>value 2</value>
</attribute>
</object>
<object name="object2">
<attribute name="attribute1"/>
<attribute name="attribute2">
<value>value 1</value>
<value>value 2</value>
</attribute>
</object>
and I need to store this into the following relational model:
Table object
Table attribute FK to object
Table value FK to attribute
Using the XMLTable function I came up with the following solution:
declare
-- This cursor retreives a record for each object with it's attribute values and
-- the underlying attributes as XMLtype
cursor c_object(p_xml xmltype) is
select obj.*
from xmltable('$d//object' passing p_xml as "d"
columns ... object columns ...
,attributes xmltype path 'attribute') as obj;
-- This cursor retreives a record for each attribute with it's attribute values and
-- the underlying values as XMLtype
cursor c_attributes(p_xml xmltype) is
select att.*
from xmltable('$d//object' passing p_xml as "d"
columns ... attribute columns ...
,values xmltype path 'value') as att;
-- This cursor retreives a record for each attribute with it's attribute values and
-- the underlying values as XMLtype
cursor c_values(p_xml xmltype) is
select val.*
from xmltable('$d//object' passing p_xml as "d"
,columns value varcahr2(100) path 'value') as val;
begin
-- p_xml contains the XML document
for r_obj in c_obj(p_xml) loop
-- insert an object record
insert into object...
for r_att in c_att(r_obj.attributes) loop
-- insert into attribute
insert into attribute ...
for r_val in c_val(r_att.values) loop
-- insert into values
end loop;
end loop;
end loop;
end;
Is this the proper way to do this? Or how would you do this?
