Grouping elements using XQuery distinct function in Oracle Service Bus

Example how to use XQuery in the Oracle Service Bus to perform a for-each-group function to change a flat dataset of records in a grouped recordset sorted on a unqiue value using the XQuery distinct-values function.

Source XML message:

<ns0:dir-response xmlns:ns0="http://www.rubix.nl/someNamespace">
 <ns0:file>
 <ns0:dataset>dataset_1</ns0:dataset>
 <ns0:name>name_11</ns0:name>
 </ns0:file>
 <ns0:file>
 <ns0:dataset>dataset_1</ns0:dataset>
 <ns0:name>name_12</ns0:name>
 </ns0:file>
 <ns0:file>
 <ns0:dataset>dataset_2</ns0:dataset>
 <ns0:name>name_21</ns0:name>
 </ns0:file>
 <ns0:file>
 <ns0:dataset>dataset_1</ns0:dataset>
 <ns0:name>name_13</ns0:name>
 </ns0:file>
</ns0:dir-response>

Wanted target XML message where output is grouped on value of element dataset:

<ns0:filelist xmlns:ns0="http://www.rubix.nl/someNamespace">
 <ns0:set>
 <ns0:setname>dataset_1</ns0:setname>
 <ns0:files>
 <ns0:filename>name_11</ns0:filename>
 </ns0:files>
 <ns0:files>
 <ns0:filename>name_12</ns0:filename>
 </ns0:files>
 <ns0:files>
 <ns0:filename>name_13</ns0:filename>
 </ns0:files>
 </ns0:set>
 <ns0:set>
 <ns0:setname>dataset_2</ns0:setname>
 <ns0:files>
 <ns0:filename>name_21</ns0:filename>
 </ns0:files>
 </ns0:set>
</ns0:filelist>

The required XQuery code:

(:: pragma bea:global-element-parameter parameter="$dir_response" element="ns0:dir-response" location="../schemas/someSource.xsd" ::)
(:: pragma bea:global-element-return element="ns0:filelist" location="../schemas/someTarget.xsd" ::)

declare namespace ns0 = "http://www.rubix.nl/someNamespace";
declare namespace xf = "http://tempuri.org/someProject/resources/xqueries/XQ_test/";

declare function xf:XQ_test($dir_response as element(ns0:dir-response))
 as element(ns0:filelist) {

 if ($dir_response/ns0:file)
 then
 <ns0:filelist>
 {
 for $setname in distinct-values($dir_response/ns0:file/ns0:dataset)
 let $file-in-set := $dir_response/ns0:file[ns0:dataset=$setname]
 return
 <ns0:set>
 <ns0:setname>{ data($setname) }</ns0:setname>
 {
 for $file in $file-in-set
 return
 <ns0:files>
 <ns0:filename>{$file/ns0:name/text()}</ns0:filename>
 </ns0:files>
 }
 </ns0:set>
 }
 </ns0:filelist>
 else <ns0:filelist/>
};

declare variable $dir_response as element(ns0:dir-response) external;

xf:XQ_test($dir_response)

About these ads