oracle中对于xmlType数据节点的增删改

select t.prp_xml.getclobval(),t.*,rowId from proposal_extend t where t.prp_code=100443579;




--增加节点
update proposal_extend t set t.prp_xml=APPENDCHILDXML(t.prp_xml, '/data/proposal',sys.xmlType.createXML('<xwc>111</xwc>')) where  t.prp_code=100443579;
update proposal_extend t set t.prp_xml=insertChildXML(t.prp_xml, '/data/proposal/xwc','@version','1') where  t.prp_code=100443579;


--删除节点
update proposal_extend t set t.prp_xml=deletexml(t.prp_xml, '/data/proposal/xwc/@version') where  t.prp_code=100443579;
update proposal_extend t set t.prp_xml=deletexml(t.prp_xml, '/data/proposal/xwc') where  t.prp_code=100443579;


--更新节点
update proposal_extend t set t.prp_xml = updatexml(t.prp_xml, '/data/proposal/xwc','<xwc>222</xwc>') where t.prp_code=100443579;
update proposal_extend t set t.prp_xml = updatexml(t.prp_xml, '/data/proposal/xwc/@version','4') where t.prp_code=100443579;


--查询节点是否存在
select Existsnode(t.prp_xml,'data/proposal/xwc') from proposal_extend t where t.prp_code=100443579;
--查询节点
select extractvalue(t.prp_xml,'data/proposal/xwc') as xwc from proposal_extend t where t.prp_code=100443579;
select extractvalue(t.prp_xml,'data/proposal/xwc/@version') as xwc from proposal_extend t where t.prp_code=100443579;
--查询多个节点
select t.prp_code,x.* from 
proposal_extend t,
xmltable('/data/milestones/milestone' passing t.prp_xml
            columns aaa varchar2(50) path 'aaa',
                         bbb varchar2(50) path 'bbb',
                         ccc varchar2(50) path 'ccc',
                         seq_no varchar2(50) path '@seq_no'
                         ) x where t.prp_code=100443579;


drop table xwc_test_xml_20161209;
--节点拆分到表
create table xwc_test_xml_20161209
as select t.prp_code,x.* from 
proposal_extend t,
xmltable('/data/milestones/milestone' passing t.prp_xml
            columns aaa varchar2(50) path 'aaa',
                         bbb varchar2(50) path 'bbb',
                         ccc varchar2(50) path 'ccc',
                         seq_no varchar2(50) path '@seq_no'
                         ) x where t.prp_code=100443579;


select t.* from xwc_test_xml_20161209 t;
--节点拼接
SELECT 
XMLElement("milestones",
                    XMLAgg(XMLElement("milestone", 
                                                    XMLAttributes(t.seq_no AS "seq_no"),
                                                    XMLForest(t.aaa AS "aaa",t.bbb as "bbb",t.ccc as "ccc")
                                                  )
                               )
              )
AS "RESULT"
FROM xwc_test_xml_20161209 t WHERE t.prp_code = 100443579;


--操作示例
update proposal_extend t set t.prp_xml=deletexml(t.prp_xml, '/data/milestones') where  t.prp_code=100443579;
declare
       v_xml_str xmltype;
begin
       SELECT 
        XMLElement("milestones",
                            XMLAgg(XMLElement("milestone", 
                                                            XMLAttributes(t.seq_no AS "seq_no"),
                                                            XMLForest(t.aaa AS "aaa",t.bbb as "bbb",t.ccc as "ccc")
                                                          )
                                       )
                       )
        into v_xml_str
        FROM xwc_test_xml_20161209 t WHERE t.prp_code = 100443579;
        update proposal_extend t set t.prp_xml=APPENDCHILDXML(t.prp_xml, '/data',v_xml_str) where  t.prp_code=100443579;
end;

 

oracle xml函数文档地址

https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF56667