How to return xml grouped by parent element?

[sboogaar]

I want to convert my _raw xml data in a query to a list of parent with an mv field of children.
Lets say the structure looks like this:

<elements>
   <element>
     <name>element one</name>
     <items>
         <item> 1   </item>
         <item> 2   </item>
         <item> 3   </item>
     </items>    
   </element>

   <element>
     <name>element two</name>
     <items>
         <item> 4   </item>
         <item> 9   </item>
     </items>    
   </element>
</elements>

What I try to get as a result is a table like:

element name                          items
--------------------------------------------------------------------------------
element one        |                 1
                   |                 2
                   |                 3
--------------------------------------------------------------------------------
element two        |                 4
                   |                 9
--------------------------------------------------------------------------------

Where items is a multi value field.
I tried using spath this gives me a list of the elements, but I cant get the items to be grouped by the element name.
Any help is welcome 🙂