CODEWITHSUNDEEP
xmlxquery
Jason
Jason

Reputation: 15

XQuery grouping of data (transforming one-to-one into one-to-many)

I have an XML element structure with a one-to-one relationship like this:

<CrewAllocation>
<Crew>Crew A</Crew>
<CrewMember>John Smith</CrewMember>
</CrewAllocation>
<CrewAllocation>
<Crew>Crew A</Crew>
<CrewMember>Jack Jones</CrewMember>
</CrewAllocation>
<CrewAllocation>
<Crew>Crew B</Crew>
<CrewMember>John Johnson</CrewMember>
</CrewAllocation>

I want to transform this into:

<CrewAllocation>
<Crew>Crew A</Crew>
<CrewMembers>
<CrewMember>John Smith</CrewMember>
<CrewMember>Jack Jones</CrewMember>
</CrewMembers>
</CrewAllocation>
<CrewAllocation>
<Crew>Crew B</Crew>
<CrewMembers>
<CrewMember>John Johnson</CrewMember>
</CrewMembers>
</CrewAllocation>

Is there an easy way to do this in XQuery? Or perhaps a better way of asking: what is the most efficient way to do this in XQuery?

Thanks!

Upvotes: 0

Views: 38

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

Here is BaseX v.9.4.4 implementation.

Also, I had to make input XML well-formed by adding a root tag.

XQuery

xquery version "3.1";
  
declare context item := document {
<root>
    <CrewAllocation>
        <Crew>Crew A</Crew>
        <CrewMember>John Smith</CrewMember>
    </CrewAllocation>
    <CrewAllocation>
        <Crew>Crew A</Crew>
        <CrewMember>Jack Jones</CrewMember>
    </CrewAllocation>
    <CrewAllocation>
        <Crew>Crew B</Crew>
        <CrewMember>John Johnson</CrewMember>
    </CrewAllocation>
</root>
};

<root>
{
  for $x in ./root/CrewAllocation
  let $crew := $x/Crew
  group by $crew
  order by $crew
  return <CrewAllocation>
            <Crew>{$crew}</Crew>
            <CrewMembers>{$x/CrewMember}</CrewMembers>
      </CrewAllocation>
}
</root>

Output

<root>
  <CrewAllocation>
    <Crew>Crew A</Crew>
    <CrewMembers>
      <CrewMember>John Smith</CrewMember>
      <CrewMember>Jack Jones</CrewMember>
    </CrewMembers>
  </CrewAllocation>
  <CrewAllocation>
    <Crew>Crew B</Crew>
    <CrewMembers>
      <CrewMember>John Johnson</CrewMember>
    </CrewMembers>
  </CrewAllocation>
</root>

Upvotes: 1

Related Questions