Xquery to group elements in OSB

This blog shows an example of how to convert a flat xml into a hierarchy by grouping them based on some element/tag.

Lets consider the following xmls. 

SourceXml - This xml contains list of orders and order lines. OID can be repeated under each OrderList with unique OLineIDs. This xml like Order lineitems for same or different Orders.

<Orders xmlns="http://www.example.org">
 <OrderList>
  <OID>333</OID>
  <OStatus>Active</OStatus>
  <OLineID>3331</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>222</OID>
  <OStatus>Active</OStatus>
  <OLineID>2222</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>333</OID>
  <OStatus>Reactivated</OStatus>
  <OLineID>3332</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>111</OID>
  <OStatus>Inactive</OStatus>
  <OLineID>1112</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>111</OID>
  <OStatus>Active</OStatus>
  <OLineID>1111</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
 <OrderList>
  <OID>333</OID>
  <OStatus>Reactivated</OStatus>
  <OLineID>3333</OLineID>
  <OLineStatus>Active</OLineStatus>
 </OrderList>
</Orders>

Now our goal is to convert it into a hierarchy, where we group all the OLineIDs under each OID into Parent-Child format. 

Sample TargetXML 

 <ns1:Order  xmlns:ns1="http://www.example.org"> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>333</ns0:OrderID> 
  <ns0:OrderStatus>Active</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3331</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3332</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>3333</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>222</ns0:OrderID> 
  <ns0:OrderStatus>Active</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>2222</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
 <ns0:OrderList  xmlns:ns0="http://www.example.org"> 
  <ns0:OrderID>111</ns0:OrderID> 
  <ns0:OrderStatus>Inactive</ns0:OrderStatus> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>1112</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
  <ns0:OrderLineItem> 
   <ns0:LineItemId>1111</ns0:LineItemId> 
   <ns0:LineItemStatus>Active</ns0:LineItemStatus> 
  </ns0:OrderLineItem> 
 </ns0:OrderList> 
</ns1:Order> 

In order to achieve this, we are going to do for loop with distinct values as follows. 

xquery version "1.0" encoding "utf-8";
(:: OracleAnnotationVersion "1.0" ::)

declare namespace ns1="http://www.example.org";
declare namespace ns0 = "http://www.example.org";
(:: import schema at "GroupTarget.xsd", "GroupSource.xsd" ::)

declare variable $wo as element() (:: schema-element(ns1:Orders) ::) external;

declare function local:func($wo as element() (:: schema-element(ns1:Orders) ::)) as element() (:: schema-element(ns1:Order) ::) {
    
     {
 for $woId in distinct-values($wo/ns0:OrderList/ns0:OID)
 let $woList := $wo/ns0:OrderList[ns0:OID=$woId]
 return
            { data($woId) }
            { data($woList[1]/ns0:OStatus) }
            {
            for $woline in $woList return
                    { data($woline/ns0:OLineID) }
                    { data($woline/ns0:OLineStatus) }
                
               }            
        }    
};

local:func($wo)

Note that there is another way of achieving this functionality by using group by.
But group by does not work with XQuery 1.0. Hence, for XQuery 1.0, we need to use distinct-values.

Hope this blog helps.