oracle 数据库解析xml

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

显示效果：