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 行记录)