本文介绍GBase 8a数据库集群对XML格式数据的读取、提取和更改写入的使用样例
目录导航
原始数据
一行包含了gcadmin 的XMl格式输出的样例。
gbase> select * from testXML;
+--------------------------------------------------+
| c |
+--------------------------------------------------+
| <?xml version="1.0" encoding="utf-8"?>
<top>
<State>ACTIVE</State>
<Nodes>
<Node>
<Name>First Node</Name>
<Ip>192.168.0.1</Ip>
</Node>
<Node>
<Name>Second Node</Name>
<Ip>192.168.0.2</Ip>
</Node>
</Nodes>
</top> |
+-----------------------------------------+
1 row in set (Elapsed: 00:00:00.30)
读取
配置参数
session级别的。
gbase> set _gbase_query_path=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
查询某个属性的值
比如Top/State,返回状态为ACTIVE。
gbase> select extractValue(c,'/top/State') from testXML;
+------------------------------+
| extractValue(c,'/top/State') |
+------------------------------+
| ACTIVE |
+------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.01)
查询某个属性的个数
比如top/nodes/node,返回有2个节点。
gbase> select extractValue(c,'count(/top/Nodes/Node)') from testXML;
+------------------------------------------+
| extractValue(c,'count(/top/Nodes/Node)') |
+------------------------------------------+
| 2 |
+------------------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.01)
查询多个属性中,某个属性的值
比如 /top/nodes/node中,第一个node的IP
gbase> select extractValue(c,'/top/Nodes/Node[$1]/Ip') from testXML;
+------------------------------------------+
| extractValue(c,'/top/Nodes/Node[$1]/Ip') |
+------------------------------------------+
| 192.168.0.1 |
+------------------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.01)
更改
通过updateXML更改,匹配到指定位置的属性,更改为新的值。
gbase> select updateXML(c,'/top/Nodes/Node[$1]/Ip','<Ip>192.168.0.101</Ip>') from testXML;
+---------------------------------------------------+
| updateXML(c,'/top/Nodes/Node[$1]/Ip','<Ip>192.168.0.101</Ip>')
+------------------------------------------------------+
| <?xml version="1.0" encoding="utf-8"?>
<top>
<State>ACTIVE</State>
<Nodes>
<Node>
<Name>First Node</Name>
<Ip>192.168.0.101</Ip>
</Node>
<Node>
<Name>Second Node</Name>
<Ip>192.168.0.2</Ip>
</Node>
</Nodes>
</top> |
+--------------------------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)