如何使用xmltable处理xml

Question

我有一个包含如下关系数据的XML

<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> 

我需要将其存储到以下关系模型中：

表对象

表属性FK到对象

表值FK到属性

使用XMLTable函数，我想出了以下解决方案：

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;    

这是正确的方法吗？或者你会怎么做呢？

Answer 1

我很抱歉，这是如此可怕的不完整，但我希望它的方向是正确的。

您的解决方案执行了大量的XML解析。它可能会起作用，但如果有大量数据需要处理，我想这将需要一段时间。对XML进行索引可能会有所帮助，并可能使您的方法可接受。

更好的解决方案可能是使用DBMS_XMLSAVE或DBMS_XMLSTORE包。