Advertisements
RSS

Grouping elements using XQuery distinct function in Oracle Service Bus

02 Nov

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)
Advertisements
 
1 Comment

Posted by on 02-11-2012 in Oracle, OSB

 

Tags: , ,

One response to “Grouping elements using XQuery distinct function in Oracle Service Bus

  1. sidh

    03-09-2013 at 15:29

    Hi,

    I am facing one problem in OSB and need help.

    In my OSB project in the replace action under transformation xquery file I am replacing 3-4 local variables values(few contains custom message, message from body, message from fault) with the body message payload fields.

    This local variables i am configuring in other replace actions where I am assigning some values on it.

    Here for each and every field in the incoming data payload I am using one replace action.

    Now instead of using this 4-5 replace action I want to use only one replace action where I will directly assign values to the output message payload body fields.

    Want to know how it is possible.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: