oracle中对小xml文件的解析方法.docx

1、oracle中对小xml文件的解析方法-生成xml层级标签: xmlelement，null或都会有结束标签，不会出现单标签select xmlelement(test, null) from dual;select xmlelement(aa, xmlelement(bb, xmlelement(cc, 1), xmlelement(dd, 2)from dual;-生成xml属性值: xmlattributes, null或不会出现对应的键名select xmlelement(test, 123, xmlelement(a1, xmlattributes(123456 test_attri

2、bute), 值1, xmlelement(a2, xmlattributes(1 test1, 2 test2, test3, null test4), 值2)from dual;-生成层级标签：xmlforest，如果需要定义标签节点属性则不能使用，null不生成对应节点-单条数据进行列名匹配取值，生成的标签不一样select xmlelement(test, xmlforest(null test1, 1 test2, 3 test3)from dual;select xmlforest(1 performance1, 1 test2, 3 test3) from dual;-生成层级标

3、签：xmlagg-多条数据，给定标签名，匹配列名对应的值生成多个元素值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;

4、-可以生成无效的xml，xml只能有一个根节点select xmlagg(xmlelement(test, a) from t;-直接写xml，格式不对会报错select xmltype.createxml(12) from dual;-rtrim()去右侧空格返回一段字符串with q as (select 1 key, a x from dual union all select 2 key, b x from dual union all select 3 key, c x from dual union all select 4 key, d x from dual)/*select

5、x,rtrim( xmlagg(xmlelement(e, key|,) order by key) as concatvalfrom q group by x;*/-extract() 摘取xml，时间，文本等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), xmlelem

6、ent(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)-x

7、ml: 1 Martin Chadderton 1000 -可以同时修改多个标签内容SELECT UPDATEXML(XMLTYPE( 1 Martin Chadderton 1000 ), /DEPT/SALARY/text(), -通过XPath表达式,指定要修改的标签元素 1100, /DEPT/EMPID/text(), AA) FROM DUAL;-区分大小写select updatexml( xmltype( 1 Martin Chadderton 1000 ), /DEPT/SALARY/text(), 1200 ) from dual;-如果标签元素没有值,则更新该标签的数据时

8、也会无效,这是Oracle bug 2962474SELECT UPDATEXML(XMLTYPE( 1 Martin Chadderton ), /DEPT/SALARY/text(), 1100) FROM DUAL;-可通过下面语句解决,重写该标签,找不到对应要修改的标签不会报错SELECT UPDATEXML(XMLTYPE( 1 Martin Chadderton ), /DEPT/SALARY, XMLTYPE(论文论著其他2) FROM DUAL;-修改属性,找不到对应要修改的属性不会报错SELECT UPDATEXML(XMLTYPE( 1 Martin Chadderton

9、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(XM

10、LTYPE( 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

11、/SALARY/text(), NULL,xmlns=xyz) FROM dual;-高级应用,指定的namespace_expr 下,更新z=10的数值DECLARE X XMLTYPE := XMLTYPE(102010);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(1020);BEGIN SELE

12、CT 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;

13、DBMS_OUTPUT.PUT_LINE(X.GETCLOBVAL);END;-删除某个节点-1.通用做法 10.1之前DECLARE x XMLTYPE := XMLTYPE(10X2010Y); BEGIN SELECT UPDATEXML(x, /a/bstarts-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, namespac

14、e_expr)DECLARE x XMLTYPE := XMLTYPE(10X2010Y); BEGIN SELECT DELETEXML(x, /a/bstarts-with(c,10),xmlns=xyz) INTO x FROM dual; dbms_output.put_line(x.getstringval); END;-如果有父节点有属性，一定要指出,或则删不了DECLARE x XMLTYPE := XMLTYPE(10X110X); BEGIN SELECT DELETEXML(x, /a/b,xmlns=xyz) INTO x FROM dual; dbms_output.p

15、ut_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;

16、-XMLCDATA 10g之前没有这个函数, CDATA节是用来告诉XML解析器将一切作为数据。如果你有一个要求发送信息,例如,有很多XML“非法”字符,如&、,-但不能包含 SELECT UPDATEXML(XMLELEMENT(parent, XMLCDATA(Here is a string with a ),/parent/text(),123) FROM dual; SELECT XMLELEMENT(parent, XMLCDATA(Here is a! string with a and a) FROM dual;-如果值为空或null不会生成CDATA SELECT XMLEL

17、EMENT(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%;-如果字段类型长度比值还短,会进行

18、截取 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(1020), /a) t;-指定返回类型SELECT * FROM XMLTABLE(/a/b PASSING XMLTYPE(10) COLUMNS b_as_varchar2

19、 VARCHAR2(2) PATH ./b, b_as_xmltype XMLTYPE PATH /, b_as VARCHAR2(3) PATH .);-获取属性select *from xmltable(/a passing xmltype(1020) 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 P

20、ASSING XMLTYPE(aabb1020) 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(1020) COLUMNS b VARCHAR2(2) PATH ns2:b, c VARCHAR2(2) PATH ns1

21、:c) t;-如果节点下有相关的标签,则可以通过下面去获取各自的值-12cSELECT c.ref, c.val FROM XMLTABLE(/a/b/c PASSING XMLTYPE(1102030) 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(11102030) COLUMNS ref INTEGER PATH ref, cxml XMLTYPE PAT

22、H 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 Rema