Oracle中解析xmltype类型字段使用方法
背景:项目从某数据交换平台获取XML数据,以Oracle的XMLType格式保存在数据库字段中,需要建立触发器、存储过程,在保存数据时解析XML字段,将数据写入其他业务表中。
参考资料:Oracle的XMLType操作文档,地址为https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#sthref465
要点总结:
(一)使用SQL语句操作XMLType格式字段的3个主要方法
表中的XMLTYPE类型字段如下图
因为涉及一些信息,我把xml处理成了如下。
<base>
<owner__EOF> </owner__EOF>
<portName>aaaaa</portName>
<sys_flow>aaaaa</sys_flow>
<applicateDate>aaaaa</applicateDate>
<sys_updater>aaaaa</sys_updater>
<vacation_type>aaaaa</vacation_type>
<sys_sysReaders>aaaaa</sys_sysReaders>
<sys_needHide>aaaaa</sys_needHide>
<user_mobile>aaaaa</user_mobile>
<sys_url>aaaaa</sys_url>
<owner_departmentLeader>aaaaa</owner_departmentLeader>
<tiid>aaaaa</tiid>
<departmentLeader>aaaaa</departmentLeader>
<sys_nodetitle>aaaaa</sys_nodetitle>
<sys_db>aaaaa</sys_db>
<end_time>aaaaa</end_time>
<sys_defaultSubForm>aaaaa</sys_defaultSubForm>
<remarks>aaaaa</remarks>
<applyUser>aaaaa</applyUser>
<applicateDepartment>aaaaa</applicateDepartment>
<sys_source>aaaaa</sys_source>
<start_time>aaaaa</start_time>
</base>
1.existsNode(XMLType实例,XPATH表达式)
(1)功能:
需要用在where条件中,通过XPATH表达式在传入的XMLType实例中进行查询,如果实例中有符合表达式的节点,则函数返回1,否则返回0
(2)使用方法:
select * from UNIFLOW_WEBAPP_INST where existsNode(sys_xml,'/base/portName')=1 and id=1
函数中的"sys_xml"为保存XMLType数据的字段名,此时existsNode作为where条件的一项来使用,可以筛选出表中的记录
2.extract(XMLType实例,XPATH表达式)
(1)功能:
需要用在select之后,from之前,用于对查询出的XMLType字段的值进行进一步提取,从而获得新的XMLType格式的结果
(2)使用方法:
select extract(sys_xml,'/base/portName') from UNIFLOW_WEBAPP_INST where existsNode(sys_xml,'/base/portName')=1 and id=1;
在通过existsNode()方法筛选出记录的基础上,通过extract方法提取出Root节点下,Body子节点中所有ContainerInfo节点的信息,作为一个新的XMLType类型的结果值
(3)注意:
如果在存储过程中,可以传入一个XMLType的变量,此时只需要使用select extract(‘传入的XMLType变量’,‘XPATH表达式’) into ‘提取出的XMLType变量’ from dual;的形式,来将传入的变量进一步进行提取并保存到新的变量中。
得到的新的XMLType结果可以应用到之后的进一步操作中。
3.extractValue(XMLType实例,XPATH表达式)
(1)功能:
extractValue()方法和extract()方法类似,只不过得到数据类型的是节点的具体值,而不是XML节点类型,相当于extract.getStringVal(),通过此方法可以直接得到具体节点值,用来进行下一步操作,如insert到其他表中,此方法是最为常用的方法。
(2)使用方法:
select extractValue(sys_xml,'/base/portName'') from UNIFLOW_WEBAPP_INST where existsNode(sys_xml,'/base/portName')=1 and id=1;
(3)注意:
此方法只能提取出单个节点的值,不能同时提取多个相同节点的值。
如果要一个节点中有多个重复的子节点,则需要通过XMLSquence转化先将XMLType数据转换为Table,然后才能以表的形式获取所有的子节点值,进而使用到游标循环等操作中(详细使用见下方)。
(二)对提取出的XML数据进行转换的2种方法
1.XMLSequence(XMLType实例)
(1)功能:
XMLSequence接收一个XMLType实例(可以用extract函数从数据库中导出并提取),按照实例中的顶层节点返回一个XMLType数组。配合Oracle的table()函数,可以将XMLType数组按照表的形式进行查询,然后可以进行count(*)、使用游标循环等操作。
(2)使用方法:
select count(*) from table(XMLSequence(extract((select sys_xml from UNIFLOW_WEBAPP_INST where id=1),'/base/portName')));
(3)注意:
XMLSequence需要和table()函数组合使用,效果和XMLTable类似
table函数的应用 表函数可接受查询语句或游标作为输入参数,并可输出多行数据
2.XMLTable(‘XPATH表达式’ passing 所在表的XMLType列
columns 列名 类型 path XPATH表达式)
(1)功能
使用XMLTable可以方便的将XMLType字段转换为表格形式来进行下一步的操作
此函数将传入的XMLType列用XPATH表达式过滤后,按照columns定义的列规则,将节点和字段对应起来构成表格
(2)使用方法:
select t.id,x.* from UNIFLOW_WEBAPP_INST t,xmltable(
'/base/portName' passing t.sys_xml
columns container_number varchar2(30) path 'text()'
) x where t.id=1;
(3)注意:
转换时要注意XML的格式要符合DTD标准
配合XPATH表达式可以实现更加复杂的筛选逻辑,如按照属性值进行筛选