How to get all the child nodes of an xml data based on parent node in Oracle PLSQL

ca201 0
Viewed 32

Is there any way that I can retrieve all the child nodes along with sub child nodes if I just provide the Parent node of an xml data. The output should not hold the Parent tag.

I tried with below query but it is also returning the parent tag in the output.

Input XML Data

<GrandGrandParent>
    <GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent>
</GrandGrandParent>

Expected Output

        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>

Actual Output

<GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent>

Query I have used

select extract(xmltype('<GrandGrandParent>
    <GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent> 
</GrandGrandParent>'),'//GrandParent')  xml from  dual
xml oracle sql
2 Answers

Try this:

select extract(xmltype('<GrandGrandParent>
    <GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent> 
</GrandGrandParent>'),'/GrandGrandParent/GrandParent/*')  xml from  dual
OldProgrammer0

EXTRACT is deprecated. Use XMLQUERY (or XMLTABLE):

SELECT XMLQUERY(
         '//GrandParent/*'
         PASSING XMLTYPE(value)
         RETURNING CONTENT
       ).getClobVal() AS child_nodes
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value CLOB);

INSERT INTO table_name (value) VALUES (
  '<GrandGrandParent>
    <GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent>
</GrandGrandParent>'
);

1 rows affected

Outputs:

CHILD_NODES
<Parent1><Child1>ABC</Child1><Child2>DEF</Child2></Parent1><Parent2><Child1>GHI</Child1><Child2>JKL</Child2><Child3><Child1>MNO</Child1><Child2>PQR</Child2></Child3></Parent2>

fiddle

MT00
Related