时间戳

首页 > 解决方案 > 我有一个表，其中一列是 XML Clob。我想将 XML 属性提取为列

sql - 我有一个表，其中一列是 XML Clob。我想将 XML 属性提取为列

问题描述

我将以下 XML 作为数据存储在表中的 1 列中。我想提取VestingInfo下的所有数据，每个源数据应该是一列。我尝试了以下方法，但它没有给出有效的标识符错误

选择计划。* 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') 计划其中 pl.prty_rqst_id = '';

XML 如下所示

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

上面的 xml 是部分的，因为它完全是一个大的，所以删除了不相关的标签。

结果元素应如下所示

重排列

标签： sqlxmlextract-value

解决方案

找到了解决方案。下面非常适合上面的查询

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

推荐阅读