[转载]oracle xml操作

/*=====================生成\修改xml========================= */

--xmlelement多个标签层级

SELECT XMLELEMENT("TEST",

                  XMLELEMENT("AA",

                             XMLELEMENT("BB", 'XXX'),

                             XMLELEMENT("CC", 'XXX')))

  FROM DUAL;

  SELECT XMLELEMENT("test")

    FROM DUAL

----XMLATTRIBUTES 多个属性

SELECT XMLELEMENT("TEST",

                  '',

                  XMLELEMENT("AA",

                             XMLATTRIBUTES('' "test_attribute"),

                             XMLELEMENT("BB",

                                        XMLATTRIBUTES('z' "attr",NULL "XO"),

                                        'XXX'),

                             XMLELEMENT("CC", 'XXX')),

                  'qwe')

  FROM DUAL;

--xmlforest ,如果要定义标签节点属性值则不能用

SELECT XMLELEMENT("TEST",XMLFOREST(NULL "WW",'' "XX",'' "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 '' 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('' "xmlns"),XMLELEMENT("y", ''),XMLELEMENT("y", '')),

XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", ''),XMLELEMENT("y", '')),

XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", ''),XMLELEMENT("y", '')))  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表达式,指定要修改的标签元素

                 '',

                 '/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()',

                 '')

  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()','')

    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()','','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('' "xmlns"),XMLELEMENT("y", ''),XMLELEMENT("y", '')),

XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", ''),XMLELEMENT("y", '')),

XMLELEMENT("x",Xmlattributes('xyz' "xmlns"), XMLELEMENT("y", ''),XMLELEMENT("y", ''))), '/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><b><c>10X</c></b><b><c>20</c></b><b><c>10Y</c></b></a>');

    BEGIN

      SELECT UPDATEXML(x, '/a/b[starts-with(c,10)]', NULL)

            INTO x

            FROM dual;

      dbms_output.put_line(REPLACE(x.getstringval,'<b/>',''));

    END;

--2.10.2及以上(DELETEXML)

--DELETEXML(xmltype_instance, xpath_expression, namespace_expr)

DECLARE

      x   XMLTYPE := XMLTYPE('<a xmlns="xyz"><b><c>10X</c></b><b><c>20</c></b><b><c>10Y</c></b></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"><b><c>10X</c></b><b xmlns="xyz"><c>110X</c></b></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()','')

  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><b>1120</b><b>21230</b></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><b>10</b><c>20</c></a>'), '/a'))) t;

--指定返回类型

SELECT *

  FROM XMLTABLE('/a/b'

                PASSING XMLTYPE('<a><b>10</b></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><b battr="1">10</b><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"><b>aa</b><c>bb</c></a><a xmlns="namespace2"><b>10</b><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"><b xmlns="namespace2">10</b><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><b><ref>1</ref><c>10</c><c>20</c><c>30</c></b></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><b><ref>1</ref><cc>1</cc><c>10</c><c>20</c><c>30</c></b></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><b></b></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"><b xmlns="q"></b></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'

;

巴特西

[转载]oracle xml操作

最新文章

热门文章