Hive-XML语言解析函数xpath_string()

1、xpath_string()
语法: xpath_string(string xmlstr,string xpath_expression)
返回值: string
说明: 默认情况下，从 xml 字符串中返回第一个匹配到的表达式节点的值。

hive> SELECT xpath_string ('<a><b>b1</b><b>b2</b></a>', '//b') FROM iteblog;
OK
b1
//指定返回匹配到哪一个节点
hive> SELECT xpath_string ('<a><b>b1</b><b>b2</b></a>', '//b[2]') FROM iteblog;
OK
b2

2、xpath()
语法: xpath(string xmlstr,string xpath_expression)
返回值: array
说明: 从 xml 字符串中返回匹配到表达式的结果数组。

//获取 xml 字符串中 a/b/节点的值
hive> select xpath('<a><b>b1</b><b>b2</b><c>c1</c></a>','a/b/text()') from iteblog;
OK
["b1","b2"]
//获取 xml 字符串中所有名为 id 的属性值
hive> select xpath('<a><b id="foo">b1</b><b id="bar">b2</b></a>','//@id') from iteblog;
OK
["foo","bar"]

//xpath_string 只返回获取 xml 字符串中第一个名为 id 的属性值
hive> select xpath_string ('<a><b id="foo">b1</b><b id="bar">b2</b></a>','//@id') from iteblog;
OK
"foo"

实际案例

--取报文数据
drop table if exists database_name.temp_table_20210630_zt;
create table database_name.temp_table_20210630_zt as
select
xpath_string(a.OUTPUT,'/Application/credit/@productCode') prod_cd, --产品编码
a.round_par,
to_date(a.irst_dt) appl_date, --申请时间
xpath_string(a.OUTPUT,'/Application/@id_credit') appl_no,--申请单号
xpath_string(a.OUTPUT,'/Application/BrMApply/@d7_id_nbank_orgnum') d7_id_nbank_orgnum, 
xpath_string(a.OUTPUT,'/Application/PythonModel/@d385Score') as d385Score,
xpath_string(a.OUTPUT,'/Application/PythonModel/@d386Score') as d386Score
from database_namexx.table_name_blaze_vect a
where xpath_string(a.OUTPUT,'/Application/credit/@productCode') = '5008'
and a.round_par = '2c'
and a.bdpms_etl >= '20210601'

————————————————
参考原文链接：https://blog.youkuaiyun.com/weixin_43215250/article/details/88416773