oracle 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)--1SELECT XMLELEMENT("KK",XMLAGG(XMLELEMENT("TEST",a))) FROM t GROUP BY b;--2SELECT 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,listaggWITH 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 concatvalFROM qGROUP BY X;--2SELECT X,       RTRIM(XMLAGG(XMLELEMENT(E, KEY || ‘,‘) ORDER BY KEY)             .EXTRACT(‘//text()‘),             ‘,‘) AS CONCATVAL  FROM Q GROUP BY X;

 

oracle xml操作