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.
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