I have a table with one column as XML Clob. I want to extract the XML attributes as columns

Question

I have below XML as data stored in 1 of the columns in my table. I want to extract all the data under VestingInfo, each source data should be a single column. I tried the below but it give not a valid identifier error

select plans.* from cth01.tablename pl, XMLTABLE(XMLNAMESPACES ('http://...' as "ns3", 'http:/...' as "ns4"), '/CTHClob/ns3:OtherData/ns4:Participant/ns4:Plans' PASSING
xmltype(pl.rqst_info) COLUMNSNumber varchar2(20) path 'ns4:Number', Pin varchar2(20) path 'ns4:Pin') plans where pl.prty_rqst_id = '';

XML looks like below

 <?xml version="1.0"?><CTHClobType xmlns:ns2="http://... xmlns:ns4....>
        --namespaces removed
            <ns3:OtherData>
                <ns4:Participant>
                    <ns4:Name>ALEXA MAKAILA JAVAVILLAGE</ns4:Name>
                    <ns4:PIN>4159505</ns4:PIN>
                    <ns4:PlanBalance>3497.15</ns4:PlanBalance>
                    <ns4:ForcedDistributionAmount>3497.15</ns4:ForcedDistributionAmount>
                    <ns4:ParticipantListType>Critical</ns4:ParticipantListType>
                    <ns4:Plans>
                        <ns4:Plan>
                            <ns4:Number>100178</ns4:Number>
                            <ns4:Name>CHILDRENS HOSPITAL OF PHIL. RETIREMENT SAVINGS PLAN - CSA</ns4:Name>
                            <ns4:Balance>3497.15</ns4:Balance>
                            <ns4:VestingInfo>
                                <ns4:DelayedVestingLite>false</ns4:DelayedVestingLite>
                                <ns4:DelayedVestingFull>true</ns4:DelayedVestingFull>
                                <ns4:VestingSourcesOnSingleSchedule>false</ns4:VestingSourcesOnSingleSchedule>
    --The above 3 should be repeted for each source
                                <ns4:Sources>
                                    <ns4:Source>
                                        <ns4:SourceID>T</ns4:SourceID>
                                        <ns4:SourceName>EMPLOYER MATCH</ns4:SourceName>
                                        <ns4:VestedPercentage>50</ns4:VestedPercentage>
                                        <ns4:VestedAmount>5647.94</ns4:VestedAmount>
                                        <ns4:UnAdjustedVestedPercent>50</ns4:UnAdjustedVestedPercent>
                                        <ns4:TIAAContractNumber>330292F5</ns4:TIAAContractNumber>
                                        <ns4:CREFContractNumber>430292F3</ns4:CREFContractNumber>
                                        <ns4:ContractName>GRA</ns4:ContractName>
                                    </ns4:Source>
                                    <ns4:Source>
                                        ...
                                    </ns4:Source>
                                </ns4:Sources>
                            </ns4:VestingInfo>

Above xml is partial as completely it a big one so removed unrelated tags.

The resulting elements should be as below

ReslutingColumns

Answer 1

Found the solution. Below works perfectly for query like above

SELECT prty_rqst_id,  vstng_src_on_sngl_sch, OMNI_PLN_NBR, sources.* 
               FROM   cth01.rqst_payload pl, 
                            XMLTABLE(xmlnamespaces (<namespaces>), 
                            '//pathToXMLTagToBeExtracted/.../' 
                            passing  xmltype(pl.rqst_info) 
                            COLUMNS 
                            OMNI_PLN_NBR VARCHAR2(6)  path 'ns4:Number'), 
                            XMLTABLE(xmlnamespaces (<namespaces>),
                            '//pathToXMLTagToBeExtracted/...//ns4:VestingInfo' 
                            passing xmltype(pl.rqst_info) 
                            COLUMNS 
                            vstng_src_on_sngl_sch VARCHAR2(50) path 'ns4:VestingSourcesOnSingleSchedule'), 
                            XMLTABLE(xmlnamespaces (<namespaces>) , 
                            '//pathToXMLTagToBeExtracted/...//ns4:VestingInfo/ns4:Sources/ns4:Source'
                            passing xmltype(pl.rqst_info) 
                            COLUMNS 
                            src_id VARCHAR2(20) path 'ns4:SourceID', 
                            src_nm VARCHAR2(50) path 'ns4:SourceName',
                            SUB_PLN_NBR VARCHAR2(16) path 'ns4:SubplanNumber',
                            vstd_pct NUMBER(20, 2) path 'ns4:VestedPercentage', 
                            vstd_amt NUMBER(20, 2) path 'ns4:VestedAmount', 
                            UPD_UNADJ_VSTD_PCT  NUMBER(15,2) path 'ns4:UpdatedUnAdjustedVestedPercent',
                            unadj_vstd_pct NUMBER(20, 2) path 'ns4:UnAdjustedVestedPercent', 
                            tiaa_cntrct_nbr VARCHAR2(50) path 'ns4:TIAAContractNumber', 
                            cref_cntrct_nbr VARCHAR2(50) path 'ns4:CREFContractNumber', 
                            cntrct_nm VARCHAR2(50) path 'ns4:ContractName' ) sources 
                WHERE  pl.prty_rqst_id = '123')