Oracle迁移到MogDB之xmlsequence

xmlsequence

XMLSEQUENCE 函数可以将 SQL 查询结果集转换为 XML 序列。MogDB中暂时没有这个函数，我们可以用 cursor_to_xml + unnest 来模拟

Oracle源端模拟

下面代码可以把查询结果转为xmldata，并解析后循环显示

DECLARE
  CURSOR cur_emp IS
    SELECT to_char(VALUE(em).getclobval()) AS xmldata
      FROM TABLE(xmlsequence(CURSOR (SELECT ename, comm, sal
                                FROM scott.emp
                               WHERE rownum <= 2))) em;
  v_no NUMBER;
BEGIN
  v_no := 0;
  FOR cur IN cur_emp LOOP
    v_no := v_no + 1;
    dbms_output.put_line(v_no);
    dbms_output.put_line(cur.xmldata);
  END LOOP;
END;

结果展示

1
<ROW>
  <ENAME>SMITH</ENAME>
  <SAL>800</SAL>
 </ROW>


2
<ROW>
  <ENAME>ALLEN</ENAME>
  <COMM>300</COMM>
  <SAL>1600</SAL>
 </ROW>

解决方案

1、通过 cursor_to_xml 转为xml
2、cursor_to_xml 转的xml 少了root，可以通过 xmlelement 补全
3、通过xpath返回元素数组
4、通过unnest展开

DECLARE
  CURSOR cur_emp IS
  SELECT ename, comm, sal FROM scott.emp WHERE rownum <= 2;
  v_no NUMBER := 0;
  xml_data xml;
BEGIN
  OPEN cur_emp;
  --xml_data := cursor_to_xml(ref_cursor,10,false,false,'');
  --raise info '%',xml_data;
  for cur in (select unnest(xpath('/root/row',xmlelement(name "root", cursor_to_xml(cur_emp,10,false,false,'')))) as xml_row) loop
    v_no := v_no + 1;
    raise info '%',v_no;
    raise info '%',cur.xml_row;
  end loop;
END;

结果输出

INFO:  1
INFO:  <row>
  <ename>SMITH</ename>
  <sal>800.00</sal>
</row>
INFO:  2
INFO:  <row>
  <ename>ALLEN</ename>
  <comm>300.00</comm>
  <sal>1600.00</sal>
</row>
ANONYMOUS BLOCK EXECUTE
orcl=> select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 5.0.6 build 8b0a6ca8) compiled at 2024-03-27 11:05:29 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)