Data Transmission Service:EXTRACTVALUE function

The EXTRACTVALUE function is supported in Oracle to parse xml. The error message returned because the function is not supported in the PolarDB O Edition.

SQL> set linesize 300;
SQL> select * from dbmgr.xmldemo;
     A B
---------- -------------------------------------------------------------------------------------------------------------
     6 <A>3</A>

SQL> select EXTRACTVALUE(xmltype(B),'/A') from dbmgr.xmldemo;EXTRACTVALUE(XMLTYPE(B),'/A')
------------------------------------------------------------------------------------------------------------------------
3

Use xpath functions to achieve the same function. For more information, see https://www.postgresql.org/docs/11/functions-xml.html.

van=> create table xmldemo
van=> ( 
     A NUMBER,
      B VARCHAR2(100)
     );
van=> insert into xmldemo values(1,'first line');
INSERT 0 1
van=> insert into xmldemo values(2,'line 2');
INSERT 0 1
van=> SELECT xpath('/a/text()',xmlforest(a AS A)) from xmldemo ;
 xpath
 ------- 
{1}
{2}
(2 rows)
van=>