KingbaseES 原生XML系列二--XML数据操作函数(DELETEXML,APPENDCHILDXML,INSERTCHILDXML,INSERTCHILDXMLAFTER,INSERTCHILDXMLBEFORE,INSERTXMLAFTER,INSERTXMLBEFORE,UPDATEXML)
XML的简单使其易于在任何应用程序中读写数据，这使XML很快成为数据交换的一种公共语言。在不同平台下产生的信息，可以很容易加载XML数据到程序中并分析他，并以XML格式输出结果。
xml数据类型可以被用来存储XML数据。它比直接在一个text域中存储XML数据的优势在于，它会检查输入值的结构是不是良好，并且有支持函数用于在其上执行类型安全的操作。
本文详细介绍了KingbaseES中集成的相关xml数据操作函数使用。
准备数据：CREATE TABLE "public"."xmldata" ("id" integer NULL,"comm" varchar NULL,"xmlvarchar" varchar NULL,"xmldata" xml NULL,"XMLarray" xml NULL
);INSERT INTO xmldata values(1, 'zhangsan' , '<kes>aaa</kes>' , '<kes><sql>sql001</sql><rac>rac001</rac></kes>' , '<kes><version>v8r3001</version><version>v8r6001</version></kes>');
INSERT INTO xmldata values(2, 'lisi' , '<kes>bbb</kes>' , '<kes><sql>sql002</sql><rac>rac002</rac></kes>' , '<kes><version>v8r3002</version><version>v8r6002</version></kes>');
INSERT INTO xmldata values(3, 'wangwu' , '<kes>ccc</kes>' , '<kes><sql>sql003</sql><rac>rac003</rac></kes>' , '<kes><version>v8r3003</version><version>v8r6003</version></kes>');xml函数列表
- DELETEXML
- APPENDCHILDXML
- INSERTCHILDXML
- INSERTCHILDXMLAFTER
- INSERTCHILDXMLBEFORE
- INSERTXMLAFTER
- INSERTXMLBEFORE
- UPDATEXML
json函数简介
DELETEXML
功能：
函数deletexml会删除
xml_instance实例中与xpath表达式匹配的节点。
每次删除指定的一层节点，若一层含有多个节点，则该层所有节点都将被删除。若删除节点后父节点值为空，则父节点只显示结尾部分，否则显示完整父节点及父节点的其他值。
用法：
deletexml(xml_instance xml, xpath text)
deletexml(xml_instance xml, xpath text, namespace _text)示例：
test=# select deletexml(xmlvarchar , '/kes') , xmlvarchar from xmldata ;deletexml | xmlvarchar
-----------+----------------| <kes>aaa</kes>| <kes>bbb</kes>| <kes>ccc</kes>
(3 行记录)test=# select deletexml(xmldata , '/kes/sql') , xmldata from xmldata ;deletexml | xmldata
---------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><rac>rac001</rac>+| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><rac>rac002</rac>+| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><rac>rac003</rac>+| </kes> |
(3 行记录)test=# select deletexml(xmlarray , '/kes/version') , xmlarray from xmldata ;deletexml | xmlarray
-----------+-----------------------------------------------------------------<kes/> | <kes><version>v8r3001</version><version>v8r6001</version></kes><kes/> | <kes><version>v8r3002</version><version>v8r6002</version></kes><kes/> | <kes><version>v8r3003</version><version>v8r6003</version></kes>
(3 行记录)APPENDCHILDXML
功能：
函数appendchildxml将
value_expr提供的值作为xpath节点的子节点追加到xml_instance中。成功则返回追加后的xml_instance数据，失败则返回ERROR。
用法：
appendchildxml(xml_instance xml, xpath text , value_expr xml)示例：
test=# select appendchildxml(xmldata , '/kes' ,xmlvarchar) , xmldata , xmlvarchar from xmldata ;appendchildxml | xmldata | xmlvarchar
---------------------+-----------------------------------------------+----------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes> | <kes>aaa</kes><sql>sql001</sql>+| | <rac>rac001</rac>+| | <kes>aaa</kes> +| | </kes> | | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes> | <kes>bbb</kes><sql>sql002</sql>+| | <rac>rac002</rac>+| | <kes>bbb</kes> +| | </kes> | | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes> | <kes>ccc</kes><sql>sql003</sql>+| | <rac>rac003</rac>+| | <kes>ccc</kes> +| | </kes> | |
(3 行记录)INSERTCHILDXML
功能：
函数insertchildxml将
value_expr提供的值作为xpath指定节点的子节点插入到xml_instance中。成功则返回插入后的xml_instance数据，失败则返回ERROR。
用法：
insertchildxml(xml_instance xml, xpath text, child_expr text , value_expr xml)
insertchildxml(xml_instance xml, xpath text , child_expr text ,value_expr xml, namespace _text)示例：
test=# select insertchildxml(xmldata , '/kes' , 'cluster' , '<cluster>nodes</cluster>') ,xmldata from xmldata ;insertchildxml | xmldata
----------------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><sql>sql001</sql> +| <rac>rac001</rac> +| <cluster>nodes</cluster>+| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><sql>sql002</sql> +| <rac>rac002</rac> +| <cluster>nodes</cluster>+| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><sql>sql003</sql> +| <rac>rac003</rac> +| <cluster>nodes</cluster>+| </kes> |
(3 行记录)-- 数组数据插入
test=# select insertchildxml(XMLarray , '/kes' , 'cluster' , '<cluster>nodes</cluster>') , XMLarray from xmldata ;insertchildxml | XMLarray
------------------------------+-----------------------------------------------------------------<kes> +| <kes><version>v8r3001</version><version>v8r6001</version></kes><version>v8r3001</version>+| <version>v8r6001</version>+| <cluster>nodes</cluster> +| </kes> | <kes> +| <kes><version>v8r3002</version><version>v8r6002</version></kes><version>v8r3002</version>+| <version>v8r6002</version>+| <cluster>nodes</cluster> +| </kes> | <kes> +| <kes><version>v8r3003</version><version>v8r6003</version></kes><version>v8r3003</version>+| <version>v8r6003</version>+| <cluster>nodes</cluster> +| </kes> |
(3 行记录)INSERTCHILDXMLAFTER
功能：
函数insertchildxmlafter将
value_expr提供的一个或多个集合元素作为xpath指定的目标父元素的子元素插入到child_expr指定的现有集合元素之后。成功则返回插入后的xml_instance数据，失败则返回ERROR。
用法：
insertchildxmlafter(xml_instance xml, xpath text, child_expr text , value_expr xml)
insertchildxmlafter(xml_instance xml, xpath text , child_expr text ,value_expr xml,namespace _text)示例：
test=# select insertchildxmlafter(xmldata , '/kes','sql','<cluster>nodes</cluster>') ,xmldata from xmldata ;insertchildxmlafter | xmldata
----------------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><sql>sql001</sql> +| <cluster>nodes</cluster>+| <rac>rac001</rac> +| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><sql>sql002</sql> +| <cluster>nodes</cluster>+| <rac>rac002</rac> +| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><sql>sql003</sql> +| <cluster>nodes</cluster>+| <rac>rac003</rac> +| </kes> |
(3 行记录)-- 数组数据插入
test=# select insertchildxmlafter(xmlarray ,'/kes','version[1]' ,'<cluster>nodes</cluster>') ,xmlarray from xmldata ;insertchildxmlafter | xmlarray
------------------------------+-----------------------------------------------------------------<kes> +| <kes><version>v8r3001</version><version>v8r6001</version></kes><version>v8r3001</version>+| <cluster>nodes</cluster> +| <version>v8r6001</version>+| </kes> | <kes> +| <kes><version>v8r3002</version><version>v8r6002</version></kes><version>v8r3002</version>+| <cluster>nodes</cluster> +| <version>v8r6002</version>+| </kes> | <kes> +| <kes><version>v8r3003</version><version>v8r6003</version></kes><version>v8r3003</version>+| <cluster>nodes</cluster> +| <version>v8r6003</version>+| </kes> |
(3 行记录)INSERTCHILDXMLBEFORE
功能：
函数insertchildxmlbefore将
value_expr提供的一个或多个集合元素作为xpath指定的目标父元素的子元素插入到child_expr指定的现有集合元素之前。成功则返回插入后的xml_instance数据，失败则返回ERROR。
用法：
insertchildxmlbefore(xml_instance xml, xpath text, child_expr text , value_expr xml)
insertchildxmlbefore(xml_instance xml, xpath text , child_expr text , value_expr xml,namespace _text)示例：
test=# select insertchildxmlbefore(xmldata , '/kes','sql','<cluster>nodes</cluster>') ,xmldata from xmldata ;insertchildxmlbefore | xmldata
----------------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><cluster>nodes</cluster>+| <sql>sql001</sql> +| <rac>rac001</rac> +| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><cluster>nodes</cluster>+| <sql>sql002</sql> +| <rac>rac002</rac> +| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><cluster>nodes</cluster>+| <sql>sql003</sql> +| <rac>rac003</rac> +| </kes> |
(3 行记录)-- 数组数据插入
test=# select insertchildxmlbefore(xmlarray ,'/kes','version[1]' ,'<cluster>nodes</cluster>') ,xmlarray from xmldata ;insertchildxmlbefore | xmlarray
------------------------------+-----------------------------------------------------------------<kes> +| <kes><version>v8r3001</version><version>v8r6001</version></kes><cluster>nodes</cluster> +| <version>v8r3001</version>+| <version>v8r6001</version>+| </kes> | <kes> +| <kes><version>v8r3002</version><version>v8r6002</version></kes><cluster>nodes</cluster> +| <version>v8r3002</version>+| <version>v8r6002</version>+| </kes> | <kes> +| <kes><version>v8r3003</version><version>v8r6003</version></kes><cluster>nodes</cluster> +| <version>v8r3003</version>+| <version>v8r6003</version>+| </kes> |
(3 行记录)INSERTXMLAFTER
功能：
函数insertxmlafter将
value_expr提供的值插入到xpath指定的节点之后。成功则返回插入后的xml_instance数据，失败则返回ERROR。
用法：
insertxmlafter(xml_instance xml, xpath text , value_expr xml)
insertxmlafter(xml_instance xml, xpath text , value_expr xml,namespace _text)示例：
test=# select insertxmlafter(xmldata ,'/kes/sql' ,'<cluster>nodes</cluster>') ,xmldata from xmldata ;insertxmlafter | xmldata
----------------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><sql>sql001</sql> +| <cluster>nodes</cluster>+| <rac>rac001</rac> +| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><sql>sql002</sql> +| <cluster>nodes</cluster>+| <rac>rac002</rac> +| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><sql>sql003</sql> +| <cluster>nodes</cluster>+| <rac>rac003</rac> +| </kes> |
(3 行记录)-- 数组数据插入
test=# select insertxmlafter(xmlarray ,'/kes/version[1]' ,'<cluster>nodes</cluster>') , xmlarray from xmldata ;insertxmlafter | xmlarray
------------------------------+-----------------------------------------------------------------<kes> +| <kes><version>v8r3001</version><version>v8r6001</version></kes><version>v8r3001</version>+| <cluster>nodes</cluster> +| <version>v8r6001</version>+| </kes> | <kes> +| <kes><version>v8r3002</version><version>v8r6002</version></kes><version>v8r3002</version>+| <cluster>nodes</cluster> +| <version>v8r6002</version>+| </kes> | <kes> +| <kes><version>v8r3003</version><version>v8r6003</version></kes><version>v8r3003</version>+| <cluster>nodes</cluster> +| <version>v8r6003</version>+| </kes> |
(3 行记录)INSERTXMLBEFORE
功能：
函数insertxmlbefore将
value_expr提供的值插入到xpath指定的节点之前。成功则返回插入后的xml_instance数据，失败则返回ERROR。
用法：
insertxmlbefore(xml_instance xml, xpath text , value_expr xml)
insertxmlbefore(xml_instance xml, xpath text , value_expr xml,namespace _text)示例：
test=# select insertxmlbefore(xmldata ,'/kes/sql' ,'<cluster>nodes</cluster>') ,xmldata from xmldata ;insertxmlbefore | xmldata
----------------------------+-----------------------------------------------<kes> +| <kes><sql>sql001</sql><rac>rac001</rac></kes><cluster>nodes</cluster>+| <sql>sql001</sql> +| <rac>rac001</rac> +| </kes> | <kes> +| <kes><sql>sql002</sql><rac>rac002</rac></kes><cluster>nodes</cluster>+| <sql>sql002</sql> +| <rac>rac002</rac> +| </kes> | <kes> +| <kes><sql>sql003</sql><rac>rac003</rac></kes><cluster>nodes</cluster>+| <sql>sql003</sql> +| <rac>rac003</rac> +| </kes> |
(3 行记录)-- 数组数据插入
test=# select insertxmlbefore(xmlarray ,'/kes/version[1]' ,'<cluster>nodes</cluster>') , xmlarray from xmldata ;insertxmlbefore | xmlarray
------------------------------+-----------------------------------------------------------------<kes> +| <kes><version>v8r3001</version><version>v8r6001</version></kes><cluster>nodes</cluster> +| <version>v8r3001</version>+| <version>v8r6001</version>+| </kes> | <kes> +| <kes><version>v8r3002</version><version>v8r6002</version></kes><cluster>nodes</cluster> +| <version>v8r3002</version>+| <version>v8r6002</version>+| </kes> | <kes> +| <kes><version>v8r3003</version><version>v8r6003</version></kes><cluster>nodes</cluster> +| <version>v8r3003</version>+| <version>v8r6003</version>+| </kes> |
(3 行记录)UPDATEXML
功能：
函数updatexml将
xml_instance实例中xpath指定的节点内容替换为value_expr提供的值，成功则返回更新后的xml_instance实例，失败则返回ERROR。
用法：
updatexml(xml_instance xml, xpath text, value_expr xml)
updatexml(xml_instance xml, xpath text, value_expr text)
updatexml(xml_instance xml, xpath text, value_expr text, namespace text)示例：
test=# select updatexml(xmlvarchar ,'/kes' ,'<kes>xxx</kes>' ) , xmlvarchar from xmldata ;updatexml | xmlvarchar
----------------+----------------<kes>xxx</kes> | <kes>aaa</kes><kes>xxx</kes> | <kes>bbb</kes><kes>xxx</kes> | <kes>ccc</kes>
(3 行记录)