如何将xml中的分层数据插入到oracle表中

我有一种情况是我必须从xmltype读取分层数据并将其插入到不同的表中。 流动是xml示例。

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

我已经尝试了下面的代码，但它是为固定层次结构工作的。 我需要的是它必须努力达到第n级，以便代码的限制可以被删除。

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;

---

我确信有一个更优雅的方式来做到这一点，但这是我想出的第一件事。 内部查询展平xml层次结构，外部查询将这些值选择为单独的列。

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

链接地址: http://www.djcxy.com/p/93925.html

上一篇: how to insert Hierarchical data from xml to oracle tables

下一篇: In hierarchical data, how to select all nodes up to the root level?