/*=====================生成\修改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><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()',
'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><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'
;
转载于:https://www.cnblogs.com/AaronBear/p/11496917.html
相关资源：ORACLE操作XML函数
