/*=====================生成\修改xml========================= */--xmlelement多个标签层级SELECT XMLELEMENT("TEST",                  XMLELEMENT("AA",                             XMLELEMENT("BB", 'XXX'),                             XMLELEMENT("CC", 'XXX')))  FROM DUAL;  SELECT XMLELEMENT("test")    FROM DUAL----XMLATTRIBUTES 多个属性SELECT XMLELEMENT("TEST",                  '123',                  XMLELEMENT("AA",                             XMLATTRIBUTES('1235678' "test_attribute"),                             XMLELEMENT("BB",                                        XMLATTRIBUTES('z' "attr",NULL "XO"),                                        'XXX'),                             XMLELEMENT("CC", 'XXX')),                  'qwe')  FROM DUAL;  --xmlforest ,如果要定义标签节点属性值则不能用SELECT XMLELEMENT("TEST",XMLFOREST(NULL "WW",'1' "XX",'3' "xx")) FROM dual;--关于空值是否会生成生应的标签--1、xmlelement 空值或null都会有结束的标签不会出现 ,xmlattributes 空值或null对应的键名不会出现    SELECT XMLELEMENT("test", XMLELEMENT("test1", XMLATTRIBUTES(1 "test1_attribute"),NULL),                              XMLELEMENT("test2", XMLATTRIBUTES('' "test2_attribute"),''),                              XMLELEMENT("test3", XMLATTRIBUTES(NULL "test3_attribute")),'haha')    FROM dual;--2.xmlforest  null整个标签名不会出现,单引号会出现SELECT XMLELEMENT("test", XMLFOREST(NULL "test1",'' test3)) FROM dual;--xmlagg 生成xml片段WITH t AS (SELECT '1' a,'A' b FROM dual)-- UNION ALL SELECT '2','A' b FROM dual UNION ALL SELECT '3','B' b FROM dual)SELECT XMLELEMENT("KK",XMLAGG(XMLELEMENT("TEST",a))) FROM t GROUP BY b;SELECT XMLELEMENT("TT",(SELECT XMLAGG(XMLELEMENT("TEST",a)  ORDER BY a) FROM t)) FROM dual;--排序SELECT XMLELEMENT("KK",XMLAGG(XMLELEMENT("TEST",a) ORDER BY a DESC)) FROM t GROUP BY b;--可生成无效的xml,xml只能有一个根结节点SELECT XMLAGG(XMLELEMENT("TEST",a)) FROM t--以下会报错SELECT XMLTYPE.CreateXML('
     
      X
     
     
      y
     ') FROM dual;--聚合,可替代 sys_path_connect_by,listaggWITH Q AS (SELECT 1 KEY, 'A' X    FROM DUAL  UNION ALL  SELECT 2 KEY, 'B' X    FROM DUAL  UNION ALL  SELECT 3 KEY, 'A' X    FROM DUAL  UNION ALL  SELECT 4 KEY, 'A' X    FROM DUAL)--SELECT X,--RTRIM(--XMLAGG (XMLELEMENT(e, key||',') ORDER BY key)--) AS concatval--FROM q--GROUP BY X;SELECT X,       RTRIM(XMLAGG(XMLELEMENT(E, KEY || ',') ORDER BY KEY)             .EXTRACT('//text()'),             ',') AS CONCATVAL  FROM Q GROUP BY X;-- xmlconcat 拼接xml元素SELECT XMLCONCAT(XMLELEMENT("x", Xmlattributes('123' "xmlns"),XMLELEMENT("y", '10'),XMLELEMENT("y", '20')), XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", '20'),XMLELEMENT("y", '10')),XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", '60'),XMLELEMENT("y", '10')))  FROM dual; --UPDATEXML 修改xml标准内容和attribute--UPDATEXML(xmltype_instance, xpath_expression, value_expr, namespace_expr)--xml:
       
      
       1
        
      
       Martin Chadderton
        
      
       1000
      
      --可以同时修改多个标签内容SELECT UPDATEXML(XMLTYPE('
                               
      
       1
                                 
      
       Martin Chadderton
                                 
      
       1000
                               
     '),                 '/DEPT/SALARY/text()', --通过XPath表达式,指定要修改的标签元素                  '1100',                 '/DEPT/EMPID/text()',                 'AA')  FROM DUAL;--如果标签元素没有值,则更新该标签的数据时也会无效,这是Oracle bug 2962474SELECT UPDATEXML(XMLTYPE('
         
      
       1
            
      
       Martin Chadderton
           
          
     '),                 '/DEPT/SALARY/text()',                 '1100')  FROM DUAL;--可通过下面语句解决,重写该标签,找不到对应要修改的标签不会报错SELECT UPDATEXML(XMLTYPE('
         
      
       1
            
      
       Martin Chadderton
            
          
     '),                 '/DEPT/SALARY',                 XMLTYPE('
     
      1100
     '))  FROM DUAL;--修改属性,找不到对应要修改的属性不会报错SELECT      UPDATEXML(XMLTYPE('
                                
      
       1
                                 
      
       Martin Chadderton
                                 
      
       3
                               
     '),      '/DEPT/SALARY/@taxable', 'no','/DEPT/SALARY/text()','123')    FROM dual;--更新为null--1.SELECT      UPDATEXML(XMLTYPE('
                                
      
       1
                                 
      
       Martin Chadderton
                                
      
       1000
                               
     '),     '/DEPT/SALARY/text()', NULL)    FROM dual;--2、SELECT      UPDATEXML(XMLTYPE('
                                
      
       1
                                 
      
       Martin Chadderton
                                 
      
       1000
                               
     '),      '/DEPT/SALARY', null)    FROM dual;--更新父节点,为null则所有字节点都消失,剩下父节点SELECT      UPDATEXML(XMLTYPE('
                                
      
       1
                                 
      
       Martin Chadderton
                                 
      
       1000
                               
     '),      '/DEPT', null)    FROM dual;--去除父节点下所有字节点的值(采用通配符)--1.SELECT      UPDATEXML(XMLTYPE('
                                
      
       1
                                 
      
       Martin Chadderton
                                 
      
       1000
                               
     '),    '/DEPT//*', NULL)    FROM dual;--2.如果父节点有属性，一定要指出,或则更新不到数据SELECT      UPDATEXML(XMLTYPE('
         
      
       1
            
      
       Martin Chadderton
            
      
       1000
          
     '),     '/DEPT/EMPID/text()', NULL,     '/DEPT/EMPNAME/text()', NULL,     '/DEPT/SALARY/text()', NULL,'xmlns="xyz"')   FROM dual;--高级应用,指定的namespace_expr 下,更新z='10'的数值DECLARE    X XMLTYPE := XMLTYPE('
     
      
       10
      
      
       20
      
      
       10
      
     ');BEGIN    SELECT UPDATEXML(X, '/x/z[. = "10"]/text()','30','xmlns="xyz"')      INTO X      FROM DUAL;    DBMS_OUTPUT.PUT_LINE(X.GETCLOBVAL);END;--UPDATEXML可修改非标准xml(根节点多个)DECLARE    X XMLTYPE := XMLTYPE('
     
      
       10
      
      
       20
      
     ');BEGIN    SELECT UPDATEXML(XMLCONCAT(XMLELEMENT("x", Xmlattributes('123' "xmlns"),XMLELEMENT("y", '10'),XMLELEMENT("y", '20')), XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", '20'),XMLELEMENT("y", '10')),XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", '10'),XMLELEMENT("y", '10'))), '/x/y[. = "10"]/text()','AA','xmlns="xyz"')      INTO X      FROM DUAL;    DBMS_OUTPUT.PUT_LINE(X.GETCLOBVAL);END;--删除某个节点--1.通用做法 10.1之前DECLARE      x   XMLTYPE := XMLTYPE('
       
        10X
       
       
        20
       
       
        10Y
       ');    BEGIN      SELECT UPDATEXML(x, '/a/b[starts-with(c,10)]', NULL)            INTO x            FROM dual;      dbms_output.put_line(REPLACE(x.getstringval,'',''));    END;--2.10.2及以上(DELETEXML)--DELETEXML(xmltype_instance, xpath_expression, namespace_expr)DECLARE      x   XMLTYPE := XMLTYPE('
       
        10X
       
       
        20
       
       
        10Y
       ');    BEGIN      SELECT DELETEXML(x, '/a/b[starts-with(c,10)]','xmlns="xyz"')            INTO x            FROM dual;      dbms_output.put_line(x.getstringval);    END;--如果有父节点有属性，一定要指出,或则删不了DECLARE      x  XMLTYPE := XMLTYPE('
       
        10X
       
       
        110X
       ');    BEGIN      SELECT DELETEXML(x, '/a/b','xmlns="xyz"')        INTO x        FROM dual;      dbms_output.put_line(x.getclobval);    END;--XMLROOT 增加xml开头信息,这个目前不支持编码的指定SELECT XMLROOT(XMLELEMENT("x", dummy), VERSION '1.0' , STANDALONE YES)  FROM dual;--XMLCOMMENT 增加xml注释 EXTRACT('/')在sqlplus显示的时候格式会好看一些SELECT XMLELEMENT("x",             XMLELEMENT("x1", dummy),             XMLCOMMENT('Test Comment'),             XMLELEMENT("x2", dummy)           ).EXTRACT('/')      FROM dual;--XMLCDATA 10g之前没有这个函数, CDATA节是用来告诉XML解析器将一切作为数据。如果你有一个要求发送信息,例如,有很多XML“非法”字符,如&、
     <和>
      ,--但不能包含 ]]> SELECT UPDATEXML(XMLELEMENT("parent", XMLCDATA('Here is a string with a < and a >')),'/parent//text()','123')  FROM dual;     SELECT XMLELEMENT("parent", XMLCDATA('Here is a![[ string with a < and a'))  FROM dual;--如果值为空或null不会生成CDATA  SELECT XMLELEMENT("x", XMLCDATA(''))    FROM dual;
     

/*=====================通过xml获取数据========================= */--xmltable --XMLTABLE(
     
       PASSING 
      
       --                        COLUMNS 
        
        
          PATH 
         
          , {
           
           
             PATH 
            
             ...})select * from tt,xmltable('/ipmp/head' passing xmltype(tt.clb_content) columns reference varchar2(99) path 'reference', busiCode varchar2(99) path 'busiCode') WHERE clb_content LIKE '%ipmp%';--如果字段类型长度比值还短,会进行截取 select *from xmltable('/a/b' passing xmltype('
             112021230') columns b varchar2(2) path '.');--不用xmltable的实现方式SELECT EXTRACTVALUE(VALUE(t), '/a/b') b, EXTRACTVALUE(VALUE(t), '/a/c') aFROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('
             10
              
               20
              '), '/a'))) t;--指定返回类型SELECT * FROM XMLTABLE('/a/b' PASSING XMLTYPE('
             10') COLUMNS b_as_varchar2 VARCHAR2(2) PATH '../b', b_as_xmltype XMLTYPE PATH '/', b_as VARCHAR2(3) PATH '.');--获取属性select *from xmltable('/a' passing xmltype('
             10
              
               20
              ') columns b varchar2(2) path 'b', battr varchar2(1) path 'b/@battr', c varchar2(2) path 'c');--获取指定命名空间的相关标签select t.b, t.cfrom XMLTABLE(XMLNAMESPACES('namespace2' AS "ns1"), '/t/ns1:a' PASSING XMLTYPE('
             
              aa
               
                bb
               
              10
               
                20
               
             ') COLUMNS b VARCHAR2(2) PATH 'ns1:b', c VARCHAR2(2) PATH 'ns1:c') t;--获取指定不同命名空间的相关标签,如果xml里面有指定,在获取的时候也要指定,否则取不到数据select t.b, t.cfrom XMLTABLE(XMLNAMESPACES('namespace1' AS "ns1", 'namespace2' AS "ns2"), '/ns1:a' PASSING XMLTYPE('
             10
              
               20
              ') COLUMNS b VARCHAR2(2) PATH 'ns2:b', c VARCHAR2(2) PATH 'ns1:c') t;--如果节点下有相关的标签,则可以通过下面去获取各自的值--12cSELECT c.ref, c.val FROM XMLTABLE('/a/b/c' PASSING XMLTYPE('
             
               
                1
               
               
                10
               
               
                20
               
               
                30
               ') RETURNING SEQUENCE BY REF COLUMNS ref INTEGER PATH '../ref', val INTEGER PATH '/') p--12c以下 SELECT p.ref, c.val FROM XMLTABLE('/a/b' PASSING XMLTYPE('
             
               
                1
               
               
                1
               
               
                10
               
               
                20
               
               
                30
               ') COLUMNS ref INTEGER PATH 'ref', cxml XMLTYPE PATH 'c') p, XMLTABLE('/c' PASSING p.cxml COLUMNS val INTEGER PATH '/') c---XMLEXISTS 11g以上才有 XMLEXISTS(
             
               PASSING 
              
               ) 判断xml是否存在节点WITH x AS (SELECT XMLTYPE('
               ') a FROM dual)SELECT CASE WHEN XMLEXISTS('/a/c' PASSING x.a) THEN 'TRUE' ELSE 'FALSE' ENDFROM x;--带命名空间WITH x AS (SELECT XMLTYPE('
               ') a FROM dual)SELECT CASE WHEN XMLEXISTS('declare namespace ns1="abc";declare namespace ns2="q"; (::) /ns1:a/ns2:b' PASSING x.a) THEN 'TRUE' ELSE 'FALSE' ENDFROM x;--例子WITH t as (select xmltype('
               
                AA
               ') xmlvar from dual )select xmlvarfrom twhere XMLEXISTS ('/CODEVALUE[.="AA"]' PASSING xmlvar);WITH t as (select xmltype('
               
                 
                 
                  ClaimCause
                  
                 
                  1
                  
                 
                  A
                  
                
                 
                 
                  Claim
                  
                 
                  1
                  
                 
                  AA
                 
                
                 
                 
                  Y
                  
                 
                  Remarks
                  
                 
                  1
                 
                
               ') xmlvar from dual )select xmlvarfrom twhere XMLEXISTS ('/ROWSET/ROW/CODEVALUE[.="AA" ]' PASSING xmlvar) AND XMLEXISTS ('/ROWSET/ROW/CODETYPE[.="ClaimCause"]' PASSING xmlvar)ANDXMLEXISTS ('/ROWSET/ROW[CODEVALUE="AA" and CODETYPE="Claim"]' PASSING xmlvar);WITH t as (select xmltype(' 
                
                 
                 
                  ClaimCause
                  
                 
                  1
                  
                 
                  A
                  
                 
                 
                 
                  Claim
                  
                 
                  1
                  
                 
                  AA
                  
                 
                 
                 
                  Y
                  
                 
                  Remarks
                  
                 
                  1
                  
                 
                ') xmlvar from dual ) select xmlvar , CODETYPE, CODEVALUE from t ,xmltable ('for $i in /ROWSET/ROW return $i' passing xmlvar columns CODETYPE varchar2(100) path 'CODETYPE' ,CODEVALUE varchar2(100) path 'CODEVALUE' ) x where x.codevalue = 'AA' and x.codetype = 'ClaimCause' ;