[转载]oracle xml操作

柔光的暖阳◎ 2023-08-17 17:36 78阅读 0赞

/*=====================生成\修改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('<dummy>X</dummy><dummy>y</dummy>') FROM dual;
 
 --聚合,可替代 sys_path_connect_by,listagg
 WITH 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:
 <DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY>1000</SALARY>
 </DEPT> 
 --可以同时修改多个标签内容
 SELECT UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY>1000</SALARY>
 </DEPT>'),
 '/DEPT/SALARY/text()', --通过XPath表达式,指定要修改的标签元素 
 '1100',
 '/DEPT/EMPID/text()',
 'AA')
 FROM DUAL;
 --如果标签元素没有值,则更新该标签的数据时也会无效,这是Oracle bug 2962474
 SELECT UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY></SALARY>
 </DEPT>'),
 '/DEPT/SALARY/text()',
 '1100')
 FROM DUAL;
 --可通过下面语句解决,重写该标签,找不到对应要修改的标签不会报错
 SELECT UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY />
 </DEPT>'),
 '/DEPT/SALARY',
 XMLTYPE('<SALARY>1100</SALARY>'))
 FROM DUAL;
 --修改属性,找不到对应要修改的属性不会报错
 SELECT
 UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY taxable="yes">3</SALARY>
 </DEPT>'),
 '/DEPT/SALARY/@taxable', 'no','/DEPT/SALARY/text()','123')
 FROM dual;
 --更新为null
 --1.
 SELECT
 UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY>1000</SALARY>
 </DEPT>'),
 '/DEPT/SALARY/text()', NULL)
 FROM dual;
 --2、
 SELECT
 UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY taxable="yes">1000</SALARY>
 </DEPT>'),
 '/DEPT/SALARY', null)
 FROM dual;
 --更新父节点,为null则所有字节点都消失,剩下父节点
 SELECT
 UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY taxable="yes">1000</SALARY>
 </DEPT>'),
 '/DEPT', null)
 FROM dual;
 --去除父节点下所有字节点的值(采用通配符)
 --1.
 SELECT
 UPDATEXML(XMLTYPE('<DEPT>
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY>1000</SALARY>
 </DEPT>'),
 '/DEPT//*', NULL)
 FROM dual;
 --2.如果父节点有属性，一定要指出,或则更新不到数据
 SELECT
 UPDATEXML(XMLTYPE('<DEPT xmlns="xyz">
 <EMPID>1</EMPID>
 <EMPNAME>Martin Chadderton</EMPNAME>
 <SALARY>1000</SALARY>
 </DEPT>'),
 '/DEPT/EMPID/text()', NULL,
 '/DEPT/EMPNAME/text()', NULL,
 '/DEPT/SALARY/text()', NULL,'xmlns="xyz"')
 FROM dual;
 
 --高级应用,指定的namespace_expr 下,更新z='10'的数值
 DECLARE
 X XMLTYPE := XMLTYPE('<x xmlns="xyz"><y>10</y><z>20</z><z>10</z></x>');
 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('<x xmlns="xyz"><y>10</y><y>20</y></x>');
 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('<a><c>10X</c><c>20</c><c>10Y</c></a>');
 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('<a xmlns="xyz"><c>10X</c><c>20</c><c>10Y</c></a>');
 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('<a xmlns="xyz"><c>10X</c><c>110X</c></a>');
 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(<xpath/query> PASSING <xmltype document>
 -- COLUMNS <column alias> <datatype> PATH <xpath/query>, {<column alias> <datatype> PATH <xpath/query>...})
 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('<a>112021230</a>')
 columns b varchar2(2) path '.');
 --不用xmltable的实现方式
 SELECT 
 EXTRACTVALUE(VALUE(t), '/a/b') b,
 EXTRACTVALUE(VALUE(t), '/a/c') a
 FROM 
 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<a>10<c>20</c></a>'), '/a'))) t;
 --指定返回类型
 SELECT * 
 FROM XMLTABLE('/a/b' 
 PASSING XMLTYPE('<a>10</a>')
 COLUMNS b_as_varchar2 VARCHAR2(2) PATH '../b',
 b_as_xmltype XMLTYPE PATH '/',
 b_as VARCHAR2(3) PATH '.');
 
 --获取属性
 select *
 from xmltable('/a' passing xmltype('<a>10<c>20</c></a>')
 columns b varchar2(2) path 'b',
 battr varchar2(1) path 'b/@battr',
 c varchar2(2) path 'c');
 --获取指定命名空间的相关标签
 select t.b, t.c
 from XMLTABLE(XMLNAMESPACES('namespace2' AS "ns1"),
 '/t/ns1:a' 
 PASSING XMLTYPE('<t><a xmlns="namespace1">aa<c>bb</c></a><a xmlns="namespace2">10<c>20</c></a></t>')
 COLUMNS b VARCHAR2(2) PATH 'ns1:b',
 c VARCHAR2(2) PATH 'ns1:c') t;
 
 --获取指定不同命名空间的相关标签,如果xml里面有指定,在获取的时候也要指定,否则取不到数据
 select t.b, t.c
 from XMLTABLE(XMLNAMESPACES('namespace1' AS "ns1", 'namespace2' AS "ns2"),
 '/ns1:a' 
 PASSING XMLTYPE('<a xmlns="namespace1">10<c>20</c></a>')
 COLUMNS b VARCHAR2(2) PATH 'ns2:b',
 c VARCHAR2(2) PATH 'ns1:c') t;--如果节点下有相关的标签,则可以通过下面去获取各自的值
 --12c
 SELECT c.ref, c.val
 FROM XMLTABLE('/a/b/c'
 PASSING XMLTYPE('<a><ref>1</ref><c>10</c><c>20</c><c>30</c></a>')
 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('<a><ref>1</ref><cc>1</cc><c>10</c><c>20</c><c>30</c></a>')
 COLUMNS ref INTEGER PATH 'ref',
 cxml XMLTYPE PATH 'c') p,
 XMLTABLE('/c'
 PASSING p.cxml
 COLUMNS val INTEGER PATH '/') c
 
 ---XMLEXISTS 11g以上才有 XMLEXISTS(<xpath/xquery> PASSING <xmltype document>) 判断xml是否存在节点
 WITH x AS (
 SELECT XMLTYPE('<a></a>') a FROM dual
 )
 SELECT CASE WHEN XMLEXISTS('/a/c' PASSING x.a) THEN 'TRUE' ELSE 'FALSE' END
 FROM x;
 
 --带命名空间
 WITH x AS (
 SELECT XMLTYPE('<a xmlns="abc"></a>') 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' END
 FROM x;
 
 --例子
 WITH t as (select xmltype('<CODEVALUE>AA</CODEVALUE>') xmlvar from dual )
 select xmlvar
 from t
 where XMLEXISTS ('/CODEVALUE[.="AA"]' PASSING xmlvar);
 
 WITH t as (select xmltype('
 <ROWSET>
 <ROW>
 <CODETYPE>ClaimCause</CODETYPE>
 <CODESEQNO>1</CODESEQNO>
 <CODEVALUE>A</CODEVALUE>
 </ROW>
 <ROW>
 <CODETYPE>Claim</CODETYPE>
 <CODESEQNO>1</CODESEQNO>
 <CODEVALUE>AA</CODEVALUE>
 </ROW>
 <ROW>
 <VALID>Y</VALID>
 <CODETYPE>Remarks</CODETYPE>
 <CODESEQNO>1</CODESEQNO>
 </ROW>
 </ROWSET>
 ') xmlvar from dual )
 select xmlvar
 from t
 where XMLEXISTS ('/ROWSET/ROW/CODEVALUE[.="AA" ]' PASSING xmlvar)
 AND XMLEXISTS ('/ROWSET/ROW/CODETYPE[.="ClaimCause"]' PASSING xmlvar)
 AND
 XMLEXISTS ('/ROWSET/ROW[CODEVALUE="AA" and CODETYPE="Claim"]' PASSING xmlvar);
 
 
 WITH t as (select xmltype(' 
 <ROWSET> 
 <ROW> 
 <CODETYPE>ClaimCause</CODETYPE> 
 <CODESEQNO>1</CODESEQNO> 
 <CODEVALUE>A</CODEVALUE> 
 </ROW> 
 <ROW> 
 <CODETYPE>Claim</CODETYPE> 
 <CODESEQNO>1</CODESEQNO> 
 <CODEVALUE>AA</CODEVALUE> 
 </ROW> 
 <ROW> 
 <VALID>Y</VALID> 
 <CODETYPE>Remarks</CODETYPE> 
 <CODESEQNO>1</CODESEQNO> 
 </ROW> 
 </ROWSET> 
 ') 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' 
 ;

转载于:https://www.cnblogs.com/AaronBear/p/11496917.html