UDTF解析xml

UDTF查询语句

 select src_es.xml_pareser(a.outputdata,
'{"src":"/Application/@AppId","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/@AssociationID","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/@ScoreModelName","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/@FinalScore","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/ScoredCharacteristic/@CharacteristicName","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/ScoredCharacteristic/@BinLabel","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/ScoredCharacteristic/@BaselineScore","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/ScoredCharacteristic/@PartialScore","tag":"c1","type":"NN"}',
'{"src":"/Application/ScoreModelReturnInfo/ScoredCharacteristic/@MaxBinScore","tag":"c1","type":"NN"}'
) from src_es.decisionstep_tmp a where  a.ods_update_date='20191008' and a.serialnum ='jksidhaish011000000sadfasdfjd36023' and  a.outputdata is not null ;

结果数据

INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','性别','男','0.0','47.0','52.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','cityLevel_城市等级','一线城市','0.0','83.0','83.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','unclearedUnsecuredLoanNumber_未结清无担保信用贷款笔数','所有其它','0.0','0.0','79.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','stcLoancardOverMonths6M_贷记卡近6个月逾期月份数','所有其它','0.0','0.0','77.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','婚姻_年龄','未婚小于等于32','0.0','62.0','85.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','从未逾期信用卡_贷款账户距今最长账龄','所有其它','0.0','44.0','82.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','过去12个月信贷审核查询次数_近2个月查询次数','过去12个月信贷审核查询次数 等于缺失（无查询次数）','0.0','89.0','89.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','过去12个月贷款最大逾期期数_过去12个月信贷最大逾期期数','过去12个月贷款最大逾期期数等于缺失（无贷款）','0.0','53.0','94.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','近6个月信用卡平均使用率','特殊值','0.0','62.0','91.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','信用卡还款率','特殊值','0.0','74.0','95.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','申请评分模型','574.0','学历','未知','0.0','60.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_人行征信','0.0','stcLatestSelfQueryNum1M_近1个月内本人查询次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_人行征信','0.0','stcLast3loanTimes_最近3个月贷款查询次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_人行征信','0.0','mechanismQueryTimes3M_近3个月内贷款机构查询次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_人行征信','0.0','loanConsumeAccountCount_未结清消费贷及小额信贷公司贷款笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_人行征信','0.0','loanFinanceNoBankOrgCount_未结清非银行机构贷款机构数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','d7IdMoreApplyCount_7天内身份证号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','d7PhoneMoreApplyCount_7天内手机号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','m1IdMoreApplyCount_1个月内身份证号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','m1PhoneMoreApplyCount_1个月内手机号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','m3IdMoreApplyCount_3个月内身份证号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','m3PhoneMoreApplyCount_3个月内手机号多平台申请借款次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','certNoMulti7D_身份证七天内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','mobileMulti7D_手机号七天内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','certNoMulti1M_身份证一个月内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','mobileMulti1M_手机号一个月内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','certNoMulti3M_身份证三个月内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','mobileMulti3M_手机号三个月内多头申请次数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近7天内申请人手机号在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近7天内申请人身份证在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近1个月内申请人手机号在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近1个月内申请人身份证在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近3个月内申请人手机号在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','近3个月内申请人身份证在多个平台申请借款次数_非银和银行','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanCountM1_近1个月贷款笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanCountM3_近3个月贷款笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanCountM6_近6个月贷款笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanMoneyM1_近1个月贷款金额总和','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanMoneyM3_近3个月贷款金额总和','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanMoneyM6_近6个月贷款金额总和','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfUnpaidMoneyCurrmth_本月应还款','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalMoney_历史正常履约金额','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalPeriod_历史正常履约期数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalCount_历史正常履约笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalCountM3_最近3个月正常履约笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalCountM6_最近6个月正常履约笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalCountM12_最近12个月正常履约笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalPeriodM12_最近12个月正常履约期数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','pfNormalOrgM12_最近12个月正常履约机构数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanCountM12_近12个月贷款笔数','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','bhLoanMoneyM12_近12个月贷款金额总和','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApplyTimesM1_近1个月申请事件','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApproveTimesM1_近1个月发放事件','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApplyTimesM3_近3个月申请事件','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApproveTimesM3_近3个月发放事件','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApplyTimesM6_近6个月申请事件','所有其它','0.0','0.0','100.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','multiPartyLoanStatus_90天内是否有多头借贷信息','所有其它','0.0','0.0','10.0');
INSERT INTO src_es.decisionstep_tmp (c0,c1,c2,c3,c4,c5,c6,c7,c8) VALUES (
'jksidhaish011000000sadfasdfjd36023','','多头分档模型_外部征信','0.0','TianTongRealTimeScoreAppApproveTimesM6_近6个月发放事件','所有其它','0.0','0.0','100.0');