{"id":30,"date":"2014-10-29T16:03:00","date_gmt":"2014-10-29T15:03:00","guid":{"rendered":""},"modified":"2015-11-18T23:14:27","modified_gmt":"2015-11-18T22:14:27","slug":"structured-xml-output-from-oracle-sql-query","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2014\/10\/structured-xml-output-from-oracle-sql-query\/","title":{"rendered":"Structured XML output from Oracle SQL query"},"content":{"rendered":"

Got an interesting question today: There are tables TABLE1, TABLE2 and a junction table that joins them called JUNCTABLE. Need the following output as XML<\/p>\n

<table1>\n  <table2><\/table2>\n  <table2><\/table2>\n<\/table1>\n<table1>\n  <table2><\/table2>\n  <table2><\/table2>\n<\/table1>\n<\/code><\/pre>\n
I know the output could be aggregated using XMLAGG, but I have never looked into how to format the tag names and attributed in the output like requested.<\/p>\n
Data in my very simplified sample tables:<\/p>\n
SQL> select * from table1;\n\n        ID\n----------\n         1\n         2\n         3\n\nSQL> select * from table2;\n\n        ID\n----------\n        11\n        12\n        13\n        14\n\nSQL> select * from junctable;\n\n     T1_ID      T2_ID\n---------- ----------\n         1         11\n         1         12\n         2         12\n         2         13\n         3         14\n<\/code><\/pre>\n
Expected output:<\/p>\n
  <table1>\n    <table2><\/table2>\n    <table2><\/table2>\n  <\/table1>\n  <table1>\n    <table2><\/table2>\n    <table2><\/table2>\n  <\/table1>\n  <table1>\n    <table2><\/table2>\n  <\/table1>\n<\/code><\/pre>\n
Now the query itself:<\/p>\n
SELECT XMLAGG (xmlpieces)\n  FROM (  SELECT XMLELEMENT (\"table1\", xmlattributes (t1.id AS \"id\"), \n                   XMLAGG (XMLELEMENT (\"table2\", xmlattributes (t2.id AS \"id\")))\n                 ) xmlpieces\n            FROM table1 t1\n                 JOIN junctable j ON j.t1_id = t1.id\n                 JOIN table2 t2 ON t2.id = j.t2_id\n        GROUP BY t1.id);\n<\/code><\/pre>\n
And the result:<\/p>\n
  <table1>\n    <table2><\/table2>\n    <table2><\/table2>\n  <\/table1>\n  <table1>\n    <table2><\/table2>\n    <table2><\/table2>\n  <\/table1>\n  <table1>\n    <table2><\/table2>\n  <\/table1>\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Got an interesting question today: There are tables TABLE1, TABLE2 and a junction table that joins them called JUNCTABLE. Need the following output as XML <table1> <table2><\/table2> <table2><\/table2> <\/table1> <table1> <table2><\/table2> <table2><\/table2> <\/table1> I know the output could be aggregated using XMLAGG, but I have never looked into how to format the tag names and […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,9],"class_list":["post-30","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-xml"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":3,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"predecessor-version":[{"id":148,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/30\/revisions\/148"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}