sqlxmloracle-databasexmltableoracle-xml-db

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


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.


Solution

  • 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