Oracle XML functions

Few notes and examples on how to generate XML out of oracle database.

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.