【DM】XMLTABLE 的使用

一、场景
   将指定的数据格式转换为XML，并查询为列表
   数据格式：{"JiJClass":"1","Power":"1","DetailPara":"1","ChangeRptSrc":"1","SpreadCertRptSrc":"1"}
二、示例
--创建测试表
CREATE TABLE test1(value1 varchar2)
--添加数据
insert into test1(value1)values('{"JiJClass":"1","Power":"1","DetailPara":"1","ChangeRptSrc":"1","SpreadCertRptSrc":"1"}');
commit;

declare  _xml_VALUE varchar2;
begin
 select
      '<note><k>'+ 
      replace(
       replace(
         replace(
           replace(
              replace(value1, '{', '')
               , '}', '')
                  , '"', ''), ',',
                  '</val></note><note><k>'), ':', 
                                          '</k><val>')
        + '</val></note>' as vxml
          into 
       _xml_VALUE
   from test1;

  with tmp as
     ( select * 
        from XMLTABLE 
             ( 
             '/note' 
             PASSING _xml_VALUE
             COLUMNS "key" VARCHAR(120) PATH 'k',
                     "val" VARCHAR(120) PATH 'val' 
             ) 
             xt
     )
     select *from tmp;
end;

---最终结果