loop / Extract nodes from clob xml column in oracle pl sql

Question

I have this xml content stored in a clob column of a table, I have to loop through the "molecule" nodes under the "reactantList" node,and store each "molecule" node into another table containing a list of molecules,

Any help please? I tried with xmltype, xmlsequence, xmltable etc but did not work, I also have to specify the namespace "xmlns=.." somewhere as an argument to xmltype I think, to be able to make it work...

<cml xmlns="http://www.chemaxon.com" version="ChemAxon file format v20.20.0, generated by vunknown" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.chemaxon.com http://www.chemaxon.com/marvin/schema/mrvSchema_20_20_0.xsd">
    <MDocument>
        <MChemicalStruct>
            <reaction>
                <arrow type="DEFAULT" x1="-8.022119140625" y1="0.8333333333333334" x2="-3.5637858072916657" y2="0.8333333333333334" />
                <reactantList>
                    <molecule molID="m1">
                        <atomArray>
                            <atom id="a1" elementType="C" x2="-13.938333333333334" y2="0.7083333333333333" />
                            <atom id="a2" elementType="O" x2="-15.478333333333333" y2="0.7083333333333333" lonePair="2" />
                        </atomArray>
                        <bondArray>
                            <bond id="b1" atomRefs2="a1 a2" order="1" />
                        </bondArray>
                    </molecule>
                    <molecule molID="m2">
                        <atomArray>
                            <atom id="a1" elementType="O" x2="-9.897119140624998" y2="0.8333333333333333" mrvValence="0" lonePair="3" />
                        </atomArray>
                        <bondArray />
                    </molecule>
                </reactantList>
                <agentList />
                <productList />
            </reaction>
        </MChemicalStruct>
        <MReactionSign toption="NOROT" fontScale="14.0" halign="CENTER" valign="CENTER" autoSize="true" id="o1">
            <Field name="text">
                <![CDATA[{D font=SansSerif,size=18,bold}+]]>
            </Field>
            <MPoint x="-11.730452473958332" y="0.6666666666666666" />
            <MPoint x="-11.217119140624998" y="0.6666666666666666" />
            <MPoint x="-11.217119140624998" y="1.18" />
            <MPoint x="-11.730452473958332" y="1.18" />
        </MReactionSign>
    </MDocument>
</cml>

Answer 1

You can use:

INSERT INTO molecules (molecule)
SELECT x.molecule
FROM   table_name t
       CROSS APPLY XMLTABLE(
         XMLNAMESPACES(
           'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
           DEFAULT 'http://www.chemaxon.com'
         ),
         '/cml/MDocument/MChemicalStruct/reaction/reactantList/molecule'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           molecule XMLTYPE PATH '.'
       ) x

db<>fiddle here