Nested xml into table

Question

I have a table defined as following

CREATE TABLE "MENU"."ITEMS" (
    "ID" NUMBER NOT NULL ENABLE, 
    "PARENT" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "OPID" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "VER" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "STATUS" NUMBER DEFAULT 0 NOT NULL ENABLE, 
    "RECORD_TIME" TIMESTAMP (9) WITH LOCAL TIME ZONE DEFAULT systimestamp NOT NULL ENABLE, 
    "MODIFIED_TIME" TIMESTAMP (6) DEFAULT systimestamp NOT NULL ENABLE, 
    "VALUE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
    "TITLE" VARCHAR2(255 BYTE)
)

Using XMLGEN I created an xml as following

<?xml version="1.0" ?> 
<MENU ENCODE="0">
    <ITEM VALUE="1" TITLE="test1">
        <ITEM VALUE="1" TITLE="test10"/>
        <ITEM VALUE="2" TITLE="test11"/>
        <ITEM VALUE="3" TITLE="test12"/>
    </ITEM>
    <ITEM VALUE="2" TITLE="test2" >
        <ITEM VALUE="1" TITLE="test21"/>
        <ITEM VALUE="2" TITLE="test22"/>
        <ITEM VALUE="3" TITLE="test23"/>
        <ITEM VALUE="4" TITLE="test24"/>
        <ITEM VALUE="5" TITLE="test25"/>
        <ITEM VALUE="6" TITLE="test26"/>
    </ITEM>
    <ITEM VALUE="3" TITLE="test28">
        <ITEM VALUE="1" TITLE="test29"/>
        <ITEM VALUE="2" TITLE="hasan"/>
    </ITEM>
    <ITEM VALUE="4" TITLE="test4"/>
    <ITEM VALUE="5" TITLE="test5"/>
    <ITEM VALUE="6" TITLE="test6"/>
    <ITEM VALUE="7" TITLE="test7"/>
    <ITEM VALUE="8" TITLE="test8"/>
    <ITEM VALUE="9" TITLE="test9"/>
</MENU>

Now I want to be able to import an xml file like above and fill the table ITEMS. I tried using xmltable and xmltype together to make a table off the xml, but the problem is that I can't get the parent/child hierarchy with this method.

select * from 
    xmltable('/menu//item'
    passing xmltype(lower(xmldata))
    columns
        value varchar2(10) path '@value',
        title varchar2(10) path '@title'
);

is there any way so I can find which item is a child of which? By loop or cursor or any mean. the time consumption is not an issue since this will be an offline job. Also, the tree goes deep, many more than 2 levels shown here.

Thanks!

Marmite Bomber · Accepted Answer · 2015-09-06 11:54:25Z

This query should give you the starting point to fill the table.

 select 
   rownum ID,
   ELEMENT_NAME, PARENT_NAME, VALUE, PARENT_VALUE, TITLE, PARENT_TITLE, ELEMENT_LEVEL
 from 
     xmltable('/menu//item'
     passing xmltype(lower('<?xml version="1.0" ?> 
 <MENU ENCODE="0">
     <ITEM VALUE="1" TITLE="test1">
         <ITEM VALUE="1" TITLE="test10"/>
         <ITEM VALUE="2" TITLE="test11"/>
         <ITEM VALUE="3" TITLE="test12"/>
     </ITEM>
     <ITEM VALUE="2" TITLE="test2" >
         <ITEM VALUE="1" TITLE="test21"/>
         <ITEM VALUE="2" TITLE="test22"/>
     </ITEM>
 </MENU>'))
     columns
         --FULL_xml  XMLType path './..',
         element_name varchar2(4000) path 'local-name(.)',
         parent_name varchar2(4000) path './../local-name(.)',
         value varchar2(10) path '@value',
         parent_value varchar2(10) path './../@value',
         title varchar2(10) path '@title',
         parent_title varchar2(10) path './../@title',
         element_level number path 'count(ancestor::*)'
 );

.

         ID ELEMENT_NAME PARENT_NAME VALUE      PARENT_VALUE TITLE      PARENT_TITLE ELEMENT_LEVEL
 ---------- ------------ ----------- ---------- ------------ ---------- ------------ -------------
          1 item         menu        1                       test1                               1 
          2 item         item        1          1            test10     test1                    2 
          3 item         item        2          1            test11     test1                    2 
          4 item         item        3          1            test12     test1                    2 
          5 item         menu        2                       test2                               1 
          6 item         item        1          2            test21     test2                    2 
          7 item         item        2          2            test22     test2                    2

The assignment if the primary key of the parent must be added, but should not be a problem, as I assume VALUE and ELEMENT_LEVEL are unique.

Collectives™ on Stack Overflow

Nested xml into table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related