how to insert Hierarchical data from xml to oracle tables

Question

I have a situation were I have to read hierarchical data from the xmltype and insert it into different tables. Flowing is the xml sample.

<AH0>
    <AH1 ds="Root Hierarchy" id="1" primary="1">
        <AC ds="Org" id="Org">
            <AC ds="Org-CO" id="Org-CO">
                <AC ds="Org-CO-D01" id="Org-CO-D01">
                    <AC ds="Org-CO-D01-G66" id="Org-CO-D01-G66">
                        <AC ds="Org-CO-D01-G66-Z0081" id="Org-CO-D01-G66-Z0081">
                            <AC ds="Org-CO-D01-G66-Z0081-R80" id="Org-CO-D01-G66-Z0081-R80">
                                <AC ds="Org-CO-D01-G66-Z0081-R80-DS01" id="Org-CO-D01-G66-Z0081-R80-DS01">
                                    <AS ds="000099" id="99"/>
                                </AC>
                            </AC>
                            <AC ds="Org-CO-D01-G66-Z0081-R85" id="Org-CO-D01-G66-Z0081-R85">
                                <AC ds="Org-CO-D01-G66-Z0081-R85-DS01" id="Org-CO-D01-G66-Z0081-R85-DS01">
                                    <AS ds="000020" id="20"/>
                                </AC>
                            </AC>
                        </AC>
                    </AC>
                </AC>
            </AC>
            <AC ds="Org-FR" id="Org-FR">
                <AC ds="Org-FR-D06" id="Org-FR-D06">
                    <AC ds="Org-FR-D06-G05" id="Org-FR-D06-G05">
                        <AC ds="Org-FR-D06-G05-Z0008" id="Org-FR-D06-G05-Z0008">
                            <AC ds="Org-FR-D06-G05-Z0008-R244-2819" id="Org-FR-D06-G05-Z0008-R244-2819">
                                <AC ds="Org-FR-D06-G05-Z0008-R244-2819-DS01" id="Org-FR-D06-G05-Z0008-R244-2819-DS01">
                                    <AS ds="000489" id="489"/>
                                    <AS ds="000499" id="499"/>
                                    <AS ds="003439" id="3439"/>
                                </AC>
                                <AC ds="Org-FR-D06-G05-Z0008-R244-2819-DS05" id="Org-FR-D06-G05-Z0008-R244-2819-DS05">
                                    <AS ds="000467" id="467"/>
                                </AC>
                            </AC>
                        </AC>
                        <AC ds="Org-FR-D06-G05-Z1008" id="Org-FR-D06-G05-Z1008">
                            <AC ds="Org-FR-D06-G05-Z1008-R1008" id="Org-FR-D06-G05-Z1008-R1008">
                                <AC ds="Org-FR-D06-G05-Z1008-R1008-DS01" id="Org-FR-D06-G05-Z1008-R1008-DS01">
                                    <AS ds="000461" id="461"/>
                                    <AS ds="000554" id="554"/>
                                </AC>
                            </AC>
                        </AC>
                    </AC>
                </AC>
            </AC>
        </AC>
    </AH1>
</AH0>

I have tried with below code but it is working for fixed hierarchy. What I need is It has to worK for nth level so that the limitation of code can be removed.

FOR rec_1 IN (SELECT ExtractValue(column_value, '/AC/@ds') as ds, ExtractValue(column_value, '/AC/@t') as t, ExtractValue(column_value, '/AC/@id') as id, EXTRACT(VALUE(P), '/AC') AS child
                    FROM TABLE(XMLSequence(XML_data.extract('//AC[@ds = "' || PARENTIDS || '"]/AC'))) p)
  LOOP

    --INSERTION PART 1
    FOR rec_t IN (SELECT ExtractValue(column_value, '/AC/@ds') as ds, ExtractValue(column_value, '/AC/@t') as t, ExtractValue(column_value, '/AC/@id') as id,
                         ExtractValue(column_value, '/AC/@l') as l, ExtractValue(column_value, '/AC/@cd') as cd, EXTRACT(VALUE(P), '/AC') AS child
                    FROM TABLE(XMLSequence(child.extract('//AC[@ds = "' || rec_1.id || '"]/AC'))) p)
    LOOP
    --INSERTION PART 2
    END LOOP;
  END LOOP;

kfinity · Accepted Answer · 2017-12-11 14:17:03Z

1

I'm sure there's a more graceful way to do this, but this is the first thing I came up with. The inner query flattens out the xml hierarchy, and the outer one selects the values as separate columns.

insert into my_table (ds, parent_ds)
    select xmlquery('//node[$l]/ds/text()' passing flat, level as "l" returning content) as ds,
        xmlquery('//node[$l]/parent/text()' passing flat, level as "l" returning content) as parent_ds
    from (
        select xmlquery(
            'for $x in .//AC
            return <node><ds>{data($x/@ds)}</ds><parent>{data($x/../@ds)}</parent></node>' 
            passing xmltype(column_value) returning content) flat
        from dual
    )
    connect by level <= to_number(xmlquery('count(//node)' passing flat returning content)) 
;

answered Dec 11, 2017 at 14:17

kfinity

9,1411 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to insert Hierarchical data from xml to oracle tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related