Go through this page. It is very self-explanatory and gives really good examples.
The following functions sufficed for all my requirements:
- XMLELEMENT
- XMLATTRIBUTES
- XMLAGG
- XMLCOMMENT
Here's a small example:
Consider the following tables
To generate XML like this:
<Orders>
<Order no="99960"><Person>Kovid Matt</Person></Order>
<Order no="12214"><Person>Kovid Matt</Person></Order>
<Order no="51107"><Person>Jack Svenda</Person></Order>
<Order no="55005"><Person>Jack Svenda</Person></Order>
<Order no="55874"><Person>Jack Svenda</Person></Order>
<Order no="21355"><Person>Amme Harris</Person></Order>
</Orders>
I used
SELECT
XMLElement("Orders",XMLAgg(XMLElement("Order",
XMLAttributes(o.OrderNo AS "no"),
XMLElement("Person",p.FirstName || ' ' || p.LastName))))
FROM Persons p
INNER JOIN Orders o
ON p.P_Id=o.P_Id
For a slightly more complex XML like this,
<Persons>
<Person name="Kovid Matt">
<City>some city</City>
<Orders>
<Order no="99960"></Order>
<Order no="12214"></Order>
</Orders>
</Person>
<Person name="Jack Svenda">
<City>some city</City>
<Orders>
<Order no="51107"></Order>
<Order no="55005"></Order>
<Order no="55874"></Order>
</Orders>
</Person>
<Person name="Amme Harris">
<City>some city</City>
<Orders>
<Order no="21355"></Order>
</Orders>
</Person>
</Persons>
I used
SELECT
XMLElement("Persons",
XMLAgg(XMLElement("Person",
XMLAttributes(p.FirstName || ' ' || p.LastName AS "name"),
XMLElement("City", p.city),
XMLElement("Orders",
(SELECT XMLAgg(XMLElement("Order",
XMLAttributes(o.OrderNo AS "no")))
FROM Orders o
WHERE p.P_Id=o.P_Id)))))
FROM Persons p
There are more utility functions that Oracle offers. I hope this post helps.
