oracle 数据库解析xml
select extractvalue(xmltype(XML列),'/cisReports/cisReport/queryConditions/item/name]') as 别名h
from INTERFACE_INFO ii,ORDER_INFO oi
where oi.id=ii.fk_order_id
and II.type >100206
and oi.ORDER_STATE>40002
- extractvalue: 只支持单个节点
- extract: 支持多节点
select extract(xmltype(ii.rsp_data),'/cisReports/cisReport/queryConditions/item/value') as "姓名",
extract(xmltype(ii.rsp_data),'/cisReports/cisReport/policeCheckInfo/item') as "风险分数",
extract(xmltype(ii.rsp_data),'/cisReports/cisReport/personAntiSpoofingInfo/riskScore') as "风险分数",
extract(xmltype(ii.rsp_data),'/cisReports/cisReport/personAntiSpoofingInfo/riskLevel') as "风险等级"
from INTERFACE_INFO ii,ORDER_INFO oi
where oi.id=ii.fk_order_id
and II.type >100206
and oi.ORDER_STATE>40002
显示效果: