How can I extract relational data from an Oracle database into a structured XML file?

Question

I have a simple table structure in an Oracle Database (11gR2). Using generic terms, I have "groups" than contain one or more "items"; i.e. a one-to-many relationship. So the "item" table has a "group_id" field, which is a foreign key to the same field on the "group" table.

Is there a query I can use to extract this data from the database as XML? I'd like the result to be something like this:

<groups>
  <group name="group1">
    <item name="item1">
    <item name="item2">
  </group>
  <group name="group2">
    <item name="item3">
    <item name="item4">
  </group>
</groups>

I started writing this in a PL/SQL procedure as a loop within a loop, i.e. looping through the groups then through the items within each group, but that seems long-winded. I was hoping there would be a query using "XMLTable" or "XMLForest" I could use for this sort of structure.

Answer 1

Using XmlAgg (to aggregate what you GROUP BY):

SELECT      CAST(
               XmlElement("groups",
                   XmlAgg(
                       XmlElement(
                           "group"
                          ,XmlAttributes(g.name as "name")
                          ,XmlAgg(
                              XmlElement(
                                   "item"
                                  ,XmlAttributes(i.name as "name")
                              )
                           )
                       )
                   )
               )
               AS VARCHAR2(4000)
            ) MY_XML
FROM        group g
 INNER JOIN item  i ON i.groupid = g.id
GROUP BY    g.name
;

See XMLAgg documentation: http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#i1032865