Oracle迁移到MogDB之XMLPARSE

jgmydsai

已于 2024-05-08 09:39:07 修改

阅读量213

点赞数 2

分类专栏： Oracle迁移到MogDB xml 文章标签：迁移 MogDB XMLPARSE oracle

于 2024-05-06 16:46:46 首次发布

本文链接：https://blog.csdn.net/jgmydsai/article/details/138499942

版权

Oracle迁移到MogDB 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

xml

5 篇文章 0 订阅

订阅专栏

背景

迁移时，在两个客户的项目中都遇到过XMLPARSE，我们来看下如何迁移。

Oracle

源端代码模拟

with xml_data as
(
select '<bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book></bookstore>' as xml_content
from dual
)
SELECT XMLPARSE(DOCUMENT xml_content WELLFORMED).getClobVal() AS extracted_data
FROM xml_data;

该代码目的为解析xml，返回里面的内容，输出结果如下：

SELECT XMLPARSE(DOCUMENT xml_content WELLFORMED).getClobVal() AS extracted_data
  7  FROM xml_data;

EXTRACTED_DATA
--------------------------------------------------------------------------------
<bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book>

MogDB

直接在MogDB中运行该语句会报错

ERROR:  syntax error at or near "WELLFORMED"
LINE 6: SELECT XMLPARSE(DOCUMENT xml_content WELLFORMED).getClobVal(...
                                             ^
Time: 15.594 ms

对于这点不用担心，MogDB也支持XMLPARSE，只是语法部分地方不一样，我们稍做更改即可。
可以去掉 WELLFORMED 与 getClobVal

with xml_data as
(
select '<bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book></bookstore>' as xml_content
from dual
)
SELECT XMLPARSE(DOCUMENT xml_content /*WELLFORMED*/)/*.getClobVal()*/ AS extracted_data
FROM xml_data;

正确输出如下：

                                        extracted_data                                        
----------------------------------------------------------------------------------------------
 <bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book></bookstore>
(1 row)

Time: 1.204 ms
orcl=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 5.0.6 build 048d70b4) compiled at 2024-03-07 05:56:09 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

Time: 0.981 ms

如果要返回结果为文本格式，可以加 ::text

orcl=# with xml_data as
orcl-# (
orcl(# select '<bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book></bookstore>' as xml_content
orcl(# from dual
orcl(# )
orcl-# SELECT XMLPARSE(DOCUMENT xml_content /*WELLFORMED*/)::text/*.getClobVal()*/ AS extracted_data
orcl-# FROM xml_data;
                                        extracted_data                                        
----------------------------------------------------------------------------------------------
 <bookstore><book><title>Harry Potter</title><author>J.K. Rowling</author></book></bookstore>
(1 row)

Time: 2.024 ms

参考：
https://docs.mogdb.io/zh/mogdb/v5.0/xml-functions