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

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 函数的参数类型
• SQL Server 检测是不是数字型数据的两种方法
• 【C语言】函数的递归调用
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 和利时MACS6通过dat文件实现任意时间范围与测点的历史数据提取与科研分析
• 深入理解spring框架：剖析多线程模式下数据库连接
• 数据可视化是如何帮助大型企业提高效率的？
• NUS CS1101S：SICP JavaScript 描述：一、使用函数构建抽象
• redis 数据库

KingbaseES 函数 XML 数据kingbasees函数xml数据 kingbasees函数xml kingbasees函数xml数据查询 kingbasees函数 数据库 文件 kingbasees函数sql last_insert_id kingbasees函数insert kingbasees数据 字符串postgresql kingbasees函数 kingbasees索引 建议 数据库 数据库 数据apply kingbasees