oracle xml操作
时间：2021-07-01 10:21:17
帮助过：3人阅读
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)
--1
SELECT XMLELEMENT("KK",XMLAGG(XMLELEMENT("TEST",a)))
FROM t
GROUP BY b;
--2
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)
--1
SELECT X,
RTRIM(
XMLAGG (XMLELEMENT(e, key||‘,‘)
ORDER BY key)
) AS concatval
FROM q
GROUP BY X;
--2
SELECT X,
RTRIM(XMLAGG(XMLELEMENT(E,
KEY || ‘,‘)
ORDER BY KEY)
.EXTRACT(‘//text()‘),
‘,‘)
AS CONCATVAL
FROM Q
GROUP BY X;
oracle xml操作
标签：
