Correct, JSON_TEXT was ment to be a placeholder for your json string and JDBC_TEXT for the jdbc response respectively.
The error you are getting comes from this line in your code, SKU is probably not defined?
new XmlSlurper().parseText(SKU).ResultSet.Row.children().each { it->
Also to make it, hopefully, easier to unterstand, here another example. This time with all the values and some comments:
def JDBC_TEXT = """<Results>
<ResultSet fetchSize="100">
<Row rowNumber="1">
<COMPANY_CODE>01</COMPANY_CODE>
<POLICY_NUMBER>TEST</POLICY_NUMBER>
<BENEFIT_SEQ>99</BENEFIT_SEQ>
<BENEFIT_TYPE>UV</BENEFIT_TYPE>
<PROCESSED_TO_DATE>99999999</PROCESSED_TO_DATE>
<STATUS_CODE/>
<STATUS_REASON/>
<STATUS_DATE>0</STATUS_DATE>
<PLAN_CODE/>
<ISSUE_DATE>0</ISSUE_DATE>
<PARENT_BENEFIT_SEQ>1</PARENT_BENEFIT_SEQ>
<PAY_UP_DATE>0</PAY_UP_DATE>
<MATURE_EXPIRE_DATE>0</MATURE_EXPIRE_DATE>
<PROCESSING_CYCLE/>
<INF_RIDER_SEQ>0</INF_RIDER_SEQ>
<LAST_CHG_DATE>0</LAST_CHG_DATE>
<LAST_CHG_TIME>538976288</LAST_CHG_TIME>
<DATE_OF_BIRTH>0</DATE_OF_BIRTH>
<ISSUE_AGE>0</ISSUE_AGE>
<UNDERWRITING_CLASS/>
<SEX_CODE/>
<INSURED_DEATH_IND/>
<SECOND_INSURED_DOB>0</SECOND_INSURED_DOB>
<SEC_INS_ISS_AGE>0</SEC_INS_ISS_AGE>
<SEC_INS_UWCLS/>
<SEC_INS_SEX_CODE/>
<SEC_INS_DEATH_IND/>
<VALUE_PER_UNIT>0.00</VALUE_PER_UNIT>
<NUMBER_OF_UNITS>0.00000</NUMBER_OF_UNITS>
<ANN_PREM_PER_UNIT>0.00000</ANN_PREM_PER_UNIT>
<MODE_PREMIUM>0.00</MODE_PREMIUM>
<COMM_MDL_FCTRS_FL/>
<BENEFIT_FEE>0.00</BENEFIT_FEE>
<NON_COMM_HELTH_FLG/>
<COMM_BASED_ON_DATE/>
<PREM_BASED_ON_DATE/>
<UNITS_NAR_FLAG/>
<CATEGORY_CODE/>
<RENEWAL_TYPE/>
<AA_MODEL_FLAG/>
<ETI_MX>0E-7</ETI_MX>
<POL_LINE_OF_BUSNSS/>
<PRODUCT_TYPE/>
<AGENT_SPLIT_CTL>0</AGENT_SPLIT_CTL>
<COMMISSION_CLASS/>
<ISSUE_STATE/>
<RESIDENT_STATE/>
<AUX_AGE>0</AUX_AGE>
<BASIC_BILL_DATE>0</BASIC_BILL_DATE>
<EFF_PREM_DATE>0</EFF_PREM_DATE>
<MEC_DATE>0</MEC_DATE>
<UNDERWRITER_CODE/>
<UNDERWRITER_TYPE/>
<REIN_CODE>0</REIN_CODE>
<PREM_TAX_BASIS_FL/>
<JOINT_FLAG/>
<ALLCTD_TARGET_PREM>0.00</ALLCTD_TARGET_PREM>
<ALLCTD_MIN_PREM>0.00</ALLCTD_MIN_PREM>
<FV_FUND_VALUE_CODE/>
<FV_BASIS1>0.00</FV_BASIS1>
<FV_BASIS2>0.00</FV_BASIS2>
<FV_INCOME1>0.00</FV_INCOME1>
<FV_INCOME2>0.00</FV_INCOME2>
<FV_BALANCE1>0.00</FV_BALANCE1>
<FV_BALANCE2>0.00</FV_BALANCE2>
<FV_TOT_LOAN_BASIS>0.00</FV_TOT_LOAN_BASIS>
<FV_TOTAL_LOAN_INC>0.00</FV_TOTAL_LOAN_INC>
<FV_TOTAL_LOAN_BAL>0.00</FV_TOTAL_LOAN_BAL>
<FV_GUAR_RATE>0.00</FV_GUAR_RATE>
<FV_GUAR_DEPOSITS>0.00</FV_GUAR_DEPOSITS>
<FV_DEP_LOADS>0.00</FV_DEP_LOADS>
<FV_GR_WITHDRAWALS>0.00</FV_GR_WITHDRAWALS>
<FV_WITHDRAWAL_LOAD>0.00</FV_WITHDRAWAL_LOAD>
<FV_COI>0.00</FV_COI>
<FV_BASE_DED>0.00</FV_BASE_DED>
<FV_PROCESS_LOADS>0.00</FV_PROCESS_LOADS>
<FV_PRODUCT_TYPE/>
<FV_TYPE/>
<FV_PROCESS_RULE/>
<FV_DEP_ALLOC/>
<FV_WTD_ALLOC/>
<FV_DED_ALLOC/>
<FV_COI_ALLOC/>
<FV_LON_ALLOC/>
<FV_PLD_ALLOC/>
<FV_LAST_VAL_DATE>0</FV_LAST_VAL_DATE>
<FV_LANDMARK_DATE>0</FV_LANDMARK_DATE>
<FV_DEP_ALLOC_FLAG/>
<FV_SPECIAL_FUND/>
<FV_ORDER_NUM>0</FV_ORDER_NUM>
<FV_COMM_CLASS/>
<FV_WAIVER_FEE/>
<FV_WAIVER_CALC/>
<FV_FILLER_LOCK/>
<UV_GUAR_COI_RATE>1.28665</UV_GUAR_COI_RATE>
<UV_CURR_COI_RATE>0.82090</UV_CURR_COI_RATE>
<UV_CURRENT_NAR>90.26115</UV_CURRENT_NAR>
<UV_TOTAL_BASIS_ISS>0.00</UV_TOTAL_BASIS_ISS>
<UV_TAX_BASIS_ISSUE>0.00</UV_TAX_BASIS_ISSUE>
<UV_EI_1>0.00</UV_EI_1>
<UV_EI_2>0.00</UV_EI_2>
<UV_EI_3>0.00</UV_EI_3>
<UV_EI_4>0.00</UV_EI_4>
<UV_EI_5>0.00</UV_EI_5>
<UV_EI_6>0.00</UV_EI_6>
<UV_EI_7>0.00</UV_EI_7>
<UV_EI_8>0.00</UV_EI_8>
<UV_EI_9>0.00</UV_EI_9>
<UV_EI_10>0.00</UV_EI_10>
<UV_EI_11>0.00</UV_EI_11>
<UV_EI_12>0.00</UV_EI_12>
<UV_ANN_EI_1>0.00</UV_ANN_EI_1>
<UV_ANN_EI_2>0.00</UV_ANN_EI_2>
<UV_ANN_EI_3>0.00</UV_ANN_EI_3>
<UV_ANN_EI_4>0.00</UV_ANN_EI_4>
<UV_ANN_EI_5>0.00</UV_ANN_EI_5>
<UV_ANN_EI_6>0.00</UV_ANN_EI_6>
<UV_ANN_EI_7>0.00</UV_ANN_EI_7>
<UV_ANN_EI_8>0.00</UV_ANN_EI_8>
<UV_ANN_EI_9>0.00</UV_ANN_EI_9>
<UV_ANN_EI_10>0.00</UV_ANN_EI_10>
<UV_ANN_EI_11>0.00</UV_ANN_EI_11>
<UV_ANN_EI_12>0.00</UV_ANN_EI_12>
<UV_TOT_SPECIAL_AMT>100000.00</UV_TOT_SPECIAL_AMT>
<UV_10201988_SP_AMT>0.00</UV_10201988_SP_AMT>
<UV_12311986_FN_VAL>0.00</UV_12311986_FN_VAL>
<UV_12311988_FN_VAL>0.00</UV_12311988_FN_VAL>
<UV_WAIVER_FEE/>
<UV_WAIVER_CALC/>
<UV_FILLER_LOCK/>
<PBEN_UPD_COUNT>0</PBEN_UPD_COUNT>
<PBEN_KEY0>30314353503030314120202020203939</PBEN_KEY0>
<ROW_COLUMN>3031435350303031412020202020393955563939393939393939202020202020202020202020202020202020202020202020202020202030312020202020202020202020202020202020202020202020202020200000000128665C0000000082090C000000009026115C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000C000000000000000C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000</ROW_COLUMN>
</Row>
</ResultSet>
</Results>
"""
def JSON_TEXT = """{
"GetBenefitDetailsResult" : {
"BenefitDetails" : [
{
"CompanyCode" : "01",
"PolicyNumber" : "TEST",
"BenefitSeq" : 99,
"BenefitType" : "UV",
"ProcessedToDate" : 99999999,
"StatusCode" : "",
"StatusReason" : "",
"StatusDate" : 0,
"PlanCode" : "",
"IssueDate" : 0,
"ParentBenefitSeq" : 1,
"PayUpDate" : 0,
"MatureExpireDate" : 0,
"ProcessingCycle" : "",
"InfRiderSeq" : 0,
"LastChgDate" : 0,
"LastChgTime" : 538976288,
"DateOfBirth" : 0,
"IssueAge" : 0,
"UnderwritingClass" : "",
"SexCode" : "",
"InsuredDeathInd" : "",
"SecondInsuredDob" : 0,
"SecInsIssAge" : 0,
"SecInsUwcls" : "",
"SecInsSexCode" : "",
"SecInsDeathInd" : "",
"ValuePerUnit" : 0.00,
"NumberOfUnits" : 0.00000,
"AnnPremPerUnit" : 0.00000,
"ModePremium" : 0.00,
"CommMdlFctrsFl" : "",
"BenefitFee" : 0.00,
"NonCommHelthFlg" : "",
"CommBasedOnDate" : "",
"PremBasedOnDate" : "",
"UnitsNarFlag" : "",
"CategoryCode" : "",
"RenewalType" : "",
"AaModelFlag" : "",
"EtiMx" : 0E-7,
"PolLineOfBusnss" : "",
"ProductType" : "",
"AgentSplitCtl" : 0,
"CommissionClass" : "",
"IssueState" : "",
"ResidentState" : "",
"AuxAge" : 0,
"BasicBillDate" : 0,
"EffPremDate" : 0,
"MecDate" : 0,
"UnderwriterCode" : "",
"UnderwriterType" : "",
"ReinCode" : 0,
"PremTaxBasisFl" : "",
"JointFlag" : "",
"AllctdTargetPrem" : 0.00,
"AllctdMinPrem" : 0.00,
"TypeCode" : "",
"ParType" : "",
"NonForfeiture" : "",
"Dividend" : "",
"OtherInfo" : "",
"AccumDividends" : 0.0,
"PremiumsPaid" : 0.0,
"DividendsCredited" : 0.0,
"TaxBasis" : 0.0,
"ExcessDividend" : "",
"UlRiderFvSeq" : 0,
"CapOytOption" : "",
"MaximizeOytFlag" : "",
"OriginalUnits" : 0.0,
"OrLPOverride" : "",
"BillLoanNetDiv" : "",
"Opt8TarFlag" : "",
"EtiRpuEndowment" : 0.0,
"EtiRpuPattern" : "",
"PpPtOytDivs" : 0.0,
"FundValue" : {
"FvFundValueCode" : "",
"FvBasis1" : 0.0,
"FvBasis2" : 0.0,
"FvIncome1" : 0.00,
"FvIncome2" : 0.00,
"FvBalance1" : 0.00,
"FvBalance2" : 0.00,
"FvTotLoanBasis" : 0.00,
"FvTotalLoanInc" : 0.00,
"FvTotalLoanBal" : 0.00,
"FvGuarRate" : 0.00,
"FvGuarDeposits" : 0.00,
"FvDepLoads" : 0.00,
"FvGrWithdrawals" : 0.00,
"FvWithdrawalLoad" : 0.00,
"FvCoi" : 0.00,
"FvBaseDed" : 0.00,
"FvProcessLoads" : 0.00,
"FvProductType" : "",
"FvType" : "",
"FvProcessRule" : "",
"FvDepAlloc" : "",
"FvWtdAlloc" : "",
"FvDedAlloc" : "",
"FvCoiAlloc" : "",
"FvLonAlloc" : "",
"FvPldAlloc" : "",
"FvLastValDate" : 0,
"FvLandmarkDate" : 0,
"FvDepAllocFlag" : "",
"FvSpecialFund" : "",
"FvOrderNum" : "0",
"FvCommClass" : "",
"FvWaiverFee" : "",
"FvWaiverCalc" : "",
"FvFillerLock" : ""
},
"UnitValue" : {
"UvGuarCoiRate" : 1.28665,
"UvCurrCoiRate" : 0.82090,
"UvCurrentNar" : 90.26115,
"UvTotalBasisIss" : 0.00,
"UvTaxBasisIssue" : 0.00,
"UvEi1" : 0.00,
"UvEi2" : 0.00,
"UvEi3" : 0.00,
"UvEi4" : 0.00,
"UvEi5" : 0.00,
"UvEi6" : 0.00,
"UvEi7" : 0.00,
"UvEi8" : 0.00,
"UvEi9" : 0.00,
"UvEi10" : 0.00,
"UvEi11" : 0.00,
"UvEi12" : 0.00,
"UvAnnEi1" : 0.00,
"UvAnnEi2" : 0.00,
"UvAnnEi3" : 0.00,
"UvAnnEi4" : 0.00,
"UvAnnEi5" : 0.00,
"UvAnnEi6" : 0.00,
"UvAnnEi7" : 0.00,
"UvAnnEi8" : 0.00,
"UvAnnEi9" : 0.00,
"UvAnnEi10" : 0.00,
"UvAnnEi11" : 0.00,
"UvAnnEi12" : 0.00,
"UvTotSpecialAmt" : 100000.00,
"Uv10201988SpAmt" : 0.00,
"Uv12311986FnVal" : 0.00,
"Uv12311988FnVal" : 0.00,
"UvWaiverFee" : "",
"UvWaiverCalc" : "",
"UvFillerLock" : ""
},
"OtherRider" : {
"OrRiderType" : "",
"OrMthlyDed" : 0.0,
"OrPuaFace" : 0.0
},
"Base" : {
"BaThirdPrtyFlag" : "",
"BaOrTotInfFlag" : "",
"BaTamraPrem" : 0.0,
"BaTamraFlag" : "",
"BaOrDivIntegra" : "",
"BaOrVanishFlag" : "",
"BaOrPuaSeq" : 0.0,
"BaOrSpecialFee" : "",
"BaOrWaiverFee" : "",
"BaOrWaiverCalc" : "",
"BaOrBenefitLock" : 0.0,
"BaOrTamraTrmnt" : "",
"BaOrCovLvlDiv" : ""
},
"ShadowBenefit" : {
"SuTypeCode" : "",
"SuSubtypeCd" : "",
"SuUnitsCtrl" : "",
"SuDeduction" : 0.0,
"SuPremiumsPaid" : 0.0,
"SuDivCredited" : 0.0,
"SuTaxBasis" : 0.0,
"SuCompSubType" : "",
"SuTotInfFlag" : "",
"SuLPOverride" : "",
"SuTamraFlag" : "",
"SuRowSource" : "",
"SuBundledBenSeq" : 0,
"SuJointSelection" : "",
"SuSpecialFee" : "",
"SuWaiverFee" : "",
"SuWaiverCalc" : "",
"SuBenefitLock" : 0,
"SuTamraTrmnt" : ""
},
"SurrenderLoad" : {
"SlTableCode" : "",
"SlRateUpAge" : 0,
"SlPercent" : 0.0,
"SlFlatAmount" : 0.0,
"SlPctCeaseDate" : 0,
"SlFlatCeaseDate" : 0,
"Sl2NdTableCode" : "",
"Sl2NdRateAgeUp" : 0,
"Sl2NdPercent" : 0.0,
"Sl2NdFlatAmount" : 0.0,
"Sl2NdPctCsDate" : 0,
"Sl2NdFltCsDate" : 0,
"SlPctMthlyDed" : 0.0,
"SlFlatMthlyDed" : 0.0,
"SlPremiumsPaid" : 0.0,
"SlDivCredited" : 0.0,
"SlTaxBasis" : 0.0,
"SlLPOverride" : "",
"SlWaiverCalc" : "",
"SlBenefitLock" : 0
},
"PaidUp" : {
"PuTypeCode" : "",
"PuIntPdToDate" : 0,
"PuAccruInt" : 0.0,
"PuAccruFaceAmt" : 0.0,
"PuPremiumsPaid" : 0.0,
"PuDivCredited" : 0.0,
"PuTaxBasis" : 0.0,
"PuSpecialFee" : "",
"PuWaiverFee" : "",
"PuWaiverCalc" : "",
"PuBenefitLock" : 0
},
"AnnuityRider" : {
"ArBillingMode" : "",
"ArBillingForm" : "",
"ArBilledToDate" : 0,
"ArPaidToDate" : 0,
"ArActBillDate" : 0,
"ArBillableAmount" : 0.00,
"ArSurrenderLoad" : 0.0,
"ArCurrAccumCash" : 0.0,
"ArLastValDate" : 0,
"ArOther" : "",
"ArVarCovrFlag" : "",
"ArWithProcDate" : 0,
"ArFvCreatnFlag" : "",
"ArPremDepRque" : "",
"ArIndexedCovrFl" : "",
"ArWaiverFee" : "",
"ArWaiverCalc" : "",
"ArBenefitLock" : 0
},
"BaseFund" : {
"BfNonForfeiture" : "",
"BfSpecifiedAmt" : 0.0,
"BfCurrentDb" : 0.0,
"BfDbOption" : "",
"BfCurrSurrLoad" : 0.0,
"BfMinimumPremium" : 0.0,
"BfTarget" : 0.0,
"BfTamraAmount" : 0.0,
"BfGuidelinLvlPr" : 0.0,
"BfGuidelinSngPr" : 0.0,
"BfLastValDate" : 0,
"BfDateNegative" : 0,
"BfThirdPrtyFlag" : "",
"BfTotInfFlag" : "",
"BfVarCovrFlag" : "",
"BfWithProcDate" : 0,
"BfFvCreatnFlag" : "",
"BfPremDepRque" : "",
"BfRothIraCbFlg" : "",
"BfBaseTargetExp" : 0.0,
"BfWaiverFee" : "",
"BfWaiverCalc" : "",
"BfBenefitLock" : 0,
"BfIndexedCovrFl" : "",
"BfTreasryCvrFlg" : "",
"BfHybridCovrFlg" : ""
},
"SpecifiedAmountIncrease" : {
"SpOriginFlag" : "",
"SpSpecifiedAmt" : 0.0,
"SpTotInfFlag" : ""
}
}
],
"GUID" : "TEST51564564DFSSFG",
"ReturnCode" : 0,
"Message" : null
}
}<"""
//first transform json into a dictionary, grouped by the key
//e.g. {"CompanyCode" : "01", "CompanyCode" : "12",}
//will be aggregated as ["CompanyCode" : ["01", "12"]]
def jsonMap = [:]
for(s in JSON_TEXT.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {
k = s.replace("\"", "").split(":")[0].trim()
v = s.replace("\"", "").split(":")[1].trim()
valueList = []
if(jsonMap.containsKey(k)) {
valueList = jsonMap.get(k)
}
valueList.add(v)
jsonMap.put(k, valueList)
}
//aggregate the xml stuff also into a map, slightly easier, since it is not nested
def jdbcMap = [:]
new XmlSlurper().parseText(JDBC_TEXT).ResultSet.Row.children().each { child->
def jdbcK = child.name().replace("_", "") //you have to replace the _ otherwise it will not match the provided JSON keys
def jdbcV = child.text()
valueList = []
if(jdbcMap.containsKey(jdbcK)) {
valueList = jdbcMap.get(jdbcK)
}
valueList.add(jdbcV)
jdbcMap.put(jdbcK, valueList)
}
//now we want to find all matches
def myMatches = []
//thanks to this stackoverflow question: https://stackoverflow.com/questions/7672262/remove-key-value-from-map-while-iterating
def iJDBC = jdbcMap.entrySet().iterator()
while(iJDBC.hasNext()) {
def kJDBC = iJDBC.next().key
def iJSON = jsonMap.entrySet().iterator()
while(iJSON.hasNext()) {
def kJSON = iJSON.next().key
if(kJSON.equalsIgnoreCase(kJDBC)) {
myMatches.add("JSON: key=${kJSON} | value=${jsonMap.get(kJSON)} >> matched >> JDBC: key=${kJDBC} | value=${jdbcMap.get(kJDBC)}")
iJDBC.remove()
iJSON.remove()
}
}
}
//so after we found all matches, show them
log.info "all matches: ${myMatches}"
//do we have any values in the JDBC response that could NOT be matched?
log.warn "left over JDBC values: ${jdbcMap}"
//do we have any values in the JSON response that could NOT be matched?
log.warn "left over JSON values: ${jsonMap}"
log.info("end")
The result should look something like this:
Tue Oct 30 07:52:23 CET 2018:INFO:all matches: [JSON: key=CompanyCode | value=[01] >> matched >> JDBC: key=COMPANYC...
Tue Oct 30 07:52:23 CET 2018:WARN:left over JDBC values: [PBENUPDCOUNT:[0], PBENKEY0:[3031435350303031412020202020393...
Tue Oct 30 07:52:23 CET 2018:WARN:left over JSON values: [GetBenefitDetailsResult:[], BenefitDetails:[], TypeCode:[],...
Tue Oct 30 07:52:23 CET 2018:INFO:end
