您的位置: 首页  >  技术问答  >  从xml中提取xml节点值使用select into into oracle中的变量赋值变量

从xml中提取xml节点值使用select into into oracle中的变量赋值变量

分类: 技术问答 • 2022-07-06 13:47:03
问题描述：

我的web应用程序将数据作为xml字符串传递给Oracle中的存储过程。 以下字符串：从xml中提取xml节点值使用select into into oracle中的变量赋值变量

" <DETAIL> 
    <LON_tInstEnt> 
    <DBrID /> 
    <BrID>124</BrID> 
    <DPrdID /> 
    <PrdID>217</PrdID> 
    <DAcctID /> 
    <AcctID>124217000002</AcctID> 
    <AlterAcctID /> 
    <BrID_FK /> 
    <PrdID_FK /> 
    <CusID_FK>979125</CusID_FK> 
    <OprBrID>124</OprBrID> 
    <TenureYears>0</TenureYears> 
    <Title /> 
    <Initial /> 
    <MemFName /> 
    <MemMName /> 
    <ProcessingDt>04-05-2017</ProcessingDt> 
    <DCusBrID /> 
    <CusBrID>124</CusBrID> 
    <DCusID /> 
    <CusID>702010</CusID> 
    <MemLName>Mr. ABHIMANYU SAHOO</MemLName> 
    <AcctLevelAdd>N</AcctLevelAdd> 
    <PurpofLn /> 
    <DAppRegNo /> 
    <AppRegNo /> 
    <DSectorCode /> 
    <SectorCode /> 
    <AppliedAmt>10000</AppliedAmt> 
    <TotEligibleAmt>10000</TotEligibleAmt> 
    <SANCTIONAMT>10000</SANCTIONAMT> 
    <LnAmt>0</LnAmt> 
    <SanctionDt>04-05-2017</SanctionDt> 
    <OpeningDt /> 
    <TenureMonths>84</TenureMonths> 
    <TenureDays>0</TenureDays> 
    <EXPIRYDT>27-05-2017</EXPIRYDT> 
    <ValueDt>04-05-2017</ValueDt> 
    <DEmpDgnCd /> 
    <EmpDgnCd /> 
    <DSanctionBy /> 
    <SanctionBy>393</SanctionBy> 
    <SanctnAuth>BO</SanctnAuth> 
    <LSONO /> 
    <CyID>INR</CyID> 
    <SplRateID /> 
    <IntVariance>LT</IntVariance> 
    <IntVarRate>0</IntVarRate> 
    <MoratoriumMonth>0</MoratoriumMonth> 
    <BalanceDays>0</BalanceDays> 
    <RepayStDt /> 
    <Remarks /> 
    <ttsfplintamt /> 
    <TTSFSFDDT /> 
    <CBOFSARFAESIINITD /> 
    <TTDAOFNOTICEISSEDT /> 
    <TTDAOFPOSSIONDT /> 
    <TTDAOFSALEDT /> 
    <ttsfVALOFPROPERTY /> 
    <CBOFMULFINANCE /> 
    <CBOFRAUD /> 
    <APPROVE>N</APPROVE> 
    <SENDTOEDIT>Y</SENDTOEDIT> 
    <CRTDBY>rakesh729</CRTDBY> 
    <CRTDDT>27-05-2017 01:47:38.8381250</CRTDDT> 
    </LON_tInstEnt> 
    <TBLCONTROL> 
    <DefaulterList /> 
    <AddressHdn><NewDataSet> 
    <Table ID="702010" ADDTYPE="1" ADDRESS="xxxxxxxxxxxxxxxx" PLACE="ROUREKELA" 
    CITY="SUNDARGARH" STATE="ORISSA" PLACECODE="769001" PHONE1="1234567890" 
    PHONE2="1234567890" EMAIL="[email protected]" MOBILE="8895939630" PAGER="" WEB="" 
    STREET="SECTOR-21" COUNTRY="INDIA" PURPOSE="CUS" STATUS="" /></NewDataSet> 
    </AddressHdn> 
    <AcctCharge /> 
    <HdnRepaySch /> 
    <IntParam> 
    <NewDataSet> <ITEMDET>  
    <IntRateType>FX</IntRateType>  
    <IRTAllowOverride>0</IRTAllowOverride>  
    <IntCalcType>SMP</IntCalcType>  
    <ICTAllowOverride>0</ICTAllowOverride>  
    <IntCompFreq /> <CompFreqType />  
    <ICmpFAllowOverride>0</ICmpFAllowOverride>  
    <ICmpFTAllowOverride>0</ICmpFTAllowOverride>  
    <IntCalcFreq>ONFREQ</IntCalcFreq>  
    <CalcFreqType>FIN</CalcFreqType>  
    <ICFAllowOverride>0</ICFAllowOverride>  
    <ICFTAllowOverride>0</ICFTAllowOverride>  
    <ProdDaysCalcType>DLY</ProdDaysCalcType>  
    <PDCTAllowOverride>0</PDCTAllowOverride> 
    <IntRepayFreq>MLY</IntRepayFreq>  
    <RepayFreqType>FIN</RepayFreqType>  
    <IRFAllowOverride>0</IRFAllowOverride>  
    <IRFTAllowOverride>0</IRFTAllowOverride>  
    <IntRepayType>EFI</IntRepayType>  
    <IRepTAllowOverride>0</IRepTAllowOverride>  
    <IntCapitalizeFreq>MLY</IntCapitalizeFreq>  
    <CapitalizeFreqType>FIN</CapitalizeFreqType>  
    <ICapFAllowOverride>0</ICapFAllowOverride>  
    <ICapFTAllowOverride>0</ICapFTAllowOverride>  
    <IntAccrualFreq>MLY</IntAccrualFreq>  
    <AccrualFreqType>FIN</AccrualFreqType>  
    <IAFAllowOverride>0</IAFAllowOverride>  
    <IAFTAllowOverride>0</IAFTAllowOverride>  
    <IntDemandFreq>NA</IntDemandFreq>  
    <DemandFreqType>FIN</DemandFreqType>  
    <IDFAllowOverride>0</IDFAllowOverride>  
    <IDFTAllowOverride>0</IDFTAllowOverride>  
    <IntProdDays>365</IntProdDays> 
    </ITEMDET></NewDataSet></IntParam> 
    <BusnsDate>04-05-2017</BusnsDate> 
    <ROI><NewDataSet> 
    <ITEMDET EffDate="01-10-2011" DurSlab="NA" AmtSlab="0.0000 TO 50000.0000 " 
    PercSlab="NA" GROSSROI="10.00" NETROI="10.00" /> 
    </NewDataSet></ROI> 
    <IntVarTmp /> 
    <IntVarTemp /> 
    <RepayTemp /> 
    <MnuID /> 
    <ScrMVal /> 
    <AddressRdOnly>Y</AddressRdOnly> 
    <GroupID /> 
    <InterestParameter /> 
    <InterestRate /> 
    <RepaymentCalc /> 
    <InstallmentAmount>166</InstallmentAmount> 
    <FirstMonInst /> 
    <RestMonthInst /> 
    <RepaymentSch /> 
    <DODeedDt /> 
    <DOCDt /> 
    <DOMDt /> 
    <EcumFrmDt /> 
    <EcumToDt /> 
    <FurEcumFrmDt /> 
    <FurEcumToDt /> 
    <Pincode /> 
    <DocType /> 
    <SubReg /> 
    <DocNum>0</DocNum> 
    <MortBy>BO</MortBy> 
    <District /> 
    <Taluka /> 
    <SecId /> 
    <PropId /> 
    <SecVal /> 
    </TBLCONTROL> 
    <CUS_tCusMst> 
    <MemFatHusFName /> 
    <MemFatHusMName /> 
    <MemId>702010</MemId> 
    <SurType /> 
    <MemFatHusLName /> 
    <DOB>09-04-1971</DOB> 
    <WhetherRepatriate>N</WhetherRepatriate> 
    </CUS_tCusMst> 
    <TBLCONTROLDEF> 
    <HDU_PK_ID /> 
    <URL /> 
    <DynTlBrChange /> 
    <DisableAll /> 
    </TBLCONTROLDEF> 
    <LON_tAcctCharges> 
    <ChargeID /> 
    <LSTAPPRVDBY /> 
    <LSTAPPRVDDT /> 
    <APPROVE>N</APPROVE> 
    <REJECT /> 
    <SENDTOEDIT>Y</SENDTOEDIT> 
    <CRTDBY>rakesh729</CRTDBY> 
    <CRTDDT>27-05-2017 01:47:38.8381250</CRTDDT> 
    </LON_tAcctCharges> 
    </DETAIL>"

在存储过程中我存储XML字符串到一个名为AccountData表。 insert into accountData（）

AccountData表只有一个名为“data”的字段。 create table accountdata（data clob）;

现在我需要读取xml字符串并使用SELECT INTO查询将值存储到两个变量 （v_SanctionAmount，v_CrtdBy）中。 我正在使用的查询如下。

SELECT cols.SanctionAmt,cols.CrtdBy 
INTO v_SanctionAmount,v_CrtdBy 
FROM accountData 
cross join XMLTABLE('/DETAIL/LON_tInstEnt' 
passing xmltype(data) 
COLUMNS sanctionAmt NUMBER(19,4), 
     crtdby  VARCHAR2(25)) cols;`

当我运行上述查询时，我得到了ORA-00905：缺少关键字。 如何读取XML节点，并将其存储到使用SELECT INTO查询

先感谢变量，

答

你有很多语法错误。试试这个：

SELECT 
    cols.* 
INTO v_SanctionAmount,v_CrtdBy 
FROM 
    accountdata, 
    XMLTABLE ('/DETAIL/LON_tInstEnt' 
     PASSING accountdata.data COLUMNS 
      "sanctionAmt" NUMBER(19,4) PATH 'SANCTIONAMT', 
      "crtdby" VARCHAR2(25) PATH 'CRTDBY' 
    ) cols

而且需要一些时间来阅读文档 - https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm

相关推荐