I did some work recently prototyping creating XML directly
from a select statement. I came across many examples using xmlserialize,
xmlelement and xmlagg.
But most of these examples were pretty simple and didn’t go
very far into creating a nested structure.
I found examples that went to two levels and used xmlagg and
a group by from the query like this.
SELECT xmlserialize(document
xmlelement("root",
xmlagg(
xmlelement("Location",
xmlelement("LocationID", l.location_id),
xmlelement("Street", l.street_address),
xmlelement("Departments",
xmlagg(
xmlelement("Department",
xmlelement("DepartmentID" , d.department_id),
xmlelement("DepartmentName", d.department_name)
)
order by d.department_id
)
)
)
)
) version '1.0'
) as "xml"
FROM departments d
JOIN locations l ON (d.location_id = l.location_id)
WHERE l.location_id in (1400, 1500, 1700)
GROUP BY l.location_id, l.street_address
Which works fine but I couldn’t see how to extend this
method past two levels. When I tried to go one level deeper I got the error
ORA-00935: group function is nested too deeply
So I had to find another way to do this. Then I found
another example which nested the SQL in the select statement.
So the equivalent of the above SQL is done like this.
SELECT xmlserialize(document
xmlelement("root",
xmlagg(
xmlelement("Location",
xmlelement("LocationID", l.location_id),
xmlelement("Street", l.street_address),
xmlelement("Departments",
(SELECT
xmlagg(
xmlelement("Department",
xmlelement("DepartmentID" , d.department_id),
xmlelement("DepartmentName", d.department_name)
)
)
FROM departments d
WHERE d.location_id = l.location_id)
)
)
)
) version '1.0'
) as "xml"
FROM locations l
WHERE l.location_id in (1400, 1500, 1700)
FROM locations l
WHERE l.location_id in
(1400, 1500, 1700)
With the style like this we can then extend this to however
many levels we need.
For example extending the above example to a third level
looks like this.
SELECT xmlserialize(document
xmlelement("root",
xmlagg(
xmlelement("Location",
xmlelement("LocationID", l.location_id),
xmlelement("Street", l.street_address),
xmlelement("Departments",
(SELECT
xmlagg(
xmlelement("Department",
xmlelement("DepartmentID" , d.department_id),
xmlelement("DepartmentName", d.department_name),
xmlelement("Employees",
(SELECT
xmlagg(
xmlelement("Employee",
xmlelement("EmployeeID" , e.employee_id),
xmlelement("EmployeeFirstName", e.first_name),
xmlelement("EmployeeLastName", e.last_name)
)
)
FROM employees e
WHERE e.department_id = d.department_id)
)
)
)
FROM departments d
WHERE d.location_id = l.location_id)
)
)
)
) version '1.0'
) as "xml"
FROM locations l
WHERE l.location_id in (1400, 1500, 1700)
thank you. I was looking for this everywhere and it's a perfect example. I was able to modify mine on the first try. I really appreciate you.
ReplyDeleteThanks a lot.
ReplyDeleteGood solution. Nice examples and it was easy to understand.
ReplyDeleteThank you.
Good examples and it was easy to understand this solution.
ReplyDeleteThank you.
Just had the same problem. Wouldn't it be nice if we could keep the code neat by using joins instead of lots of embedded sub-queries.
ReplyDeleteThank you for sharing your knowledge. This is exactly what I was looking for.
ReplyDeleteHow can you combine your xmlagg() solution to get a 3 level nested XML output but you have only one source table? However I tried I didn't get the correct result...
ReplyDeleteNot sure how the formatting will go but here is three levels based off the same table. Not sure if this is what you mean.
DeleteSELECT xmlserialize(document
xmlelement("root",
xmlagg(
xmlelement("emp",
xmlelement("EmpID", e.emp_id),
xmlelement("Surname", e.surname),
xmlelement("emp_level_2",
(SELECT
xmlagg(
xmlelement("EmpLevel2",
xmlelement("empID2", e2.emp_id),
xmlelement("empTitle2" , e2.title),
xmlelement("emp_level_3",
(SELECT
xmlagg(
xmlelement("empLevel3",
xmlelement("empID3", e3.emp_id),
xmlelement("empAudit3" , e3.update_who)
)
)
FROM emp e3
WHERE e3.emp_id = e2.emp_id)
)
)
)
FROM emp e2
WHERE e2.emp_id = e.emp_id)
)
)
)
) version '1.0'
)as "xml"
FROM emp e
WHERE e.emp_id < 100
I mean what if you want to generate the XML as it was in your original example above, but you won't join three tables (locations, departments, employees) rather than using one table?
ReplyDeleteINSERT into tmp_output
WITH a
AS (SELECT l.location_id,
l.street_address,
d.department_id,
d.department_name,
e.employee_id,
e.first_name,
e.last_name
FROM employees e, departments d, locations l
WHERE l.location_id IN (1400, 1500, 1700) AND
e.department_id(+) = d.department_id AND
d.location_id = l.location_id
ORDER BY 1, 3, 5)
SELECT *
FROM a;
As you see you have 5 rows which have location_id=1400, but in the output you have only one 1400.
In that case you should use GROUP BY. But using xmlagg() and GROUP BY() in the same SELECT statement don't generate what I expected.
It depends how you want to extract the data. I think you would drive from the locations table as it gives you the most logical XML structure. Again formatting will probably be lost.
DeleteSELECT XMLSERIALIZE (
DOCUMENT XMLELEMENT (
"root",
XMLAGG (
XMLELEMENT (
"Location",
XMLELEMENT ("LocationID",
l.location_id),
XMLELEMENT ("StreetAddress",
l.street_address),
XMLELEMENT (
"Department",
(SELECT XMLAGG (
XMLELEMENT (
"Department",
XMLELEMENT (
"DepartmentID",
d.department_id),
XMLELEMENT (
"DepartmentName",
d.department_name),
XMLELEMENT (
"Employees",
(SELECT XMLAGG (
XMLELEMENT (
"Employees",
XMLELEMENT (
"EmployeeID",
e.employee_id),
XMLELEMENT (
"FirstName",
e.first_name),
XMLELEMENT (
"LastName",
e.last_name)))
FROM employees e
WHERE e.department_id = d.department_id)
)
)
)
FROM departments d
WHERE d.location_id = l.location_id)
)
)
)
)
VERSION '1.0')
AS "xml"
FROM locations l
Thanks! This helped a lot!
ReplyDeleteExcellent example Thanks very much - really helped me out
ReplyDeleteThank you mate!
ReplyDeleteI was extremely pleased to uncover this page.
ReplyDeleteI want to to thank you for ones time just for this fantastic read!!
I definitely enjoyed every part of it and i also
have you saved to fav to check out new stuff in your web
site.