表结构如下:
要求把每个xml消息中的某个字段取出来。
方法一:把所有的xml消息导出用Java或Python解析;
方法二:用MySQl的extractvalue解析,也正是所要讲的
- 首先看看解析一条XML消息是怎么样的,在navicat中执行下面的SQL
SET @temp_xml = '
<?xml version="1.0" encoding="UTF-8"?>
<MSG xmlns="http://www.travelsky.com/2006/dcsi" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<META>
<SNDR>AODB</SNDR>
<SEQN>800495</SEQN>
<DTTM>20171226114530</DTTM>
<TYPE>FLOP</TYPE>
<STYP>STND</STYP>
</META>
<FLOP>
<FLID>4666481</FLID>
<STND>Y20</STND>
</FLOP>
</MSG>
';
select extractvalue(@temp_xml,'/MSG/FLOP/STND') res1
有关于extractvalue详细的使用方法见此链接extractvalue官方说明
执行结果:
现在要做的就是依次取出数据表里的每条xml消息都放到上面的SQL语句中执行,再把执行结果放到一张临时表就能可以了。怎么做呢?因为之前学Oracle时接触过一段时间的存储过程、函数、游标,所以自然就就想到了这些。上网查了半天发现MySQL写这些东西跟Oracle大体相同。
BEGIN
#Routine body goes here...
#定义变量
DECLARE done INT DEFAULT -1;
DECLARE content LONGTEXT;
#声明游标
DECLARE cur1 CURSOR FOR SELECT t.ORIGINALCONTENT from audit t;
#结束标志
DECLARE CONTINUE HANDLER FOR NOT found SET done = 1;
OPEN cur1;
myLoop: LOOP
FETCH cur1 INTO content;
IF done = 1 THEN
LEAVE myLoop;
END IF;
SET @temp_xml = content;
#把解析结果插入临时表
INSERT INTO res (RESULT1) select extractvalue(@temp_xml,'/MSG/FLOP/STND');
END LOOP myLoop;
select * from res;
CLOSE cur1;
END
然后,就可以从表里取出想要的数据了。
还有一个问题是,如果我想要得到一个xml标签下所有的子标签及其内容,比如:
<META>
<SNDR>AODB</SNDR>
<SEQN>800495</SEQN>
<DTTM>20171226114530</DTTM>
<TYPE>FLOP</TYPE>
<STYP>STND</STYP>
</META>
我想要META标签下所有的内容,好像还不能办到.
官方文档是这样写的:
ExtractValue() returns only CDATA, and does not return any tags that might be contained within a matching tag, nor any of their content .
一些东西,虽然学过,但是后来不怎么用就慢慢的忘记了。