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.

mcalmeida · Accepted Answer · 2013-12-13 17:26:11Z

2

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

answered Dec 13, 2013 at 17:26

mcalmeida

5724 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allan Lewis Over a year ago

So I finally got around to trying this with my data! Your solution works, but how can I include groups that don't contain any items? So I'd want something like: <groups><group name="g1"><items /></group></groups> or: <groups><group name="g1"/></groups>

Allan Lewis Over a year ago

OK I think I've worked it out using a combination of XMLForest and XMLAgg :D

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related