KingbaseES 原生XML系列二 -- XML数据操作函数
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 行记录)
KINGBASE研究院
分类:
SQL
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！