SQL & PL/SQL

Allow me to preface this with the notice that I am not familiar with XML outside of its hierarchical structure, and am not familiar with what you can do with it using formatting.

As an example, let us say you have the following table:

Object_Type | Object_Name | Descriptor |
------------------------------------------------------------
Fruit | Apple | Crunchy |
Fruit | Orange | Sour |
Utensil | Pencil | Wooden |

Now let's say you want to query this table to return an XML format, which will be used in a web site to display the information, and you want to group the display by Object_Type, so that you want an XML format like this:

<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
<Descriptor>Crunchy</Descriptor>
</Object>
<Object>
<Object Name>Orange</Object Name>
<Descriptor>Sour</Descriptor>
</Object>
</Object Group>

<Object Group>
<Object Type>Utensil</Object Type>
<Object>
<Object Name>Pencil</Object Name>
<Descriptor>Wooden</Descriptor>
</Object>
</Object Group>

However, from what I can tell, using the XMLELEMENT function, it appears the closest I can get is following:

SELECT XMLELEMENT("Object Group",
XMLELEMENT("Object Type", object_type),
XMLELEMENT("Object",
XMLELEMENT("Object Name", object_name),
XMLELEMENT("Descriptor", descriptor)
)
)
FROM object_tbl;


<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
<Descriptor>Crunchy</Descriptor>
</Object>
</Object Group>

<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Orange</Object Name>
<Descriptor>Sour</Descriptor>
</Object>
</Object Group>

<Object Group>
<Object Type>Utensil</Object Type>
<Object>
<Object Name>Pencil</Object Name>
<Descriptor>Wooden</Descriptor>
</Object>
</Object Group>

Is it possible to group it in a way so that Apple and Orange end up in the the same <Object Group>? Or is that meaningless and such grouping can be done on the web site itself by formatting the XML?

Edited by: Nick Clinite on May 14, 2013 9:59 AM