XQuery: Binary search on sorted collection

Big problem with XQuery is that - unlike SQL - it doesn't allow you to specify indexes on a collection of elements. All searches are "full table scan". What a pain. Performance becomes ridiculous when the elements of a collection are numerous.

An improvement can be obtained by sorting the collection with a "order by PK" (PK being the field on which you plan to perform your search), and using a recursive binary search on the collection:

xquery version "1.0" encoding "Cp1252";

declare namespace xf = "http://tempuri.org/FindPrice/";


declare function xf:FindPriceBinarySearch($loopDepth as xs:integer, $intProdId as xs:string, $prices as element()*, $startIndex as xs:integer, $endIndex as xs:integer)
    as element() {
        let $middleIndex := xs:integer(  xs:integer(($endIndex - $startIndex) div 2) + xs:integer($startIndex)) 
        let $middleElement := $prices[$middleIndex]
        let $middleProductId := $middleElement/product_id/text()
        return
        if ($loopDepth < 15 and $middleProductId < $intProdId) then
         xf:FindPriceBinarySearch($loopDepth + 1, $intProdId, $prices, $middleIndex, $endIndex)
        else if ($loopDepth < 15 and $middleProductId > $intProdId) then
           xf:FindPriceBinarySearch($loopDepth+ 1, $intProdId, $prices, $startIndex, $middleIndex)
        else
         $middleElement
          
};

declare function xf:FindPrice($intProdId as xs:string, $prices as element())
    as element() {
        xf:FindPriceBinarySearch(0, $intProdId, $prices//price, 1, count($prices//price))
};

declare variable $product_id as xs:string external;
declare variable $prices as element() external;

xf:FindPrice($product_id, $prices)

this xquery will ALMOST work (I know it doesn't find elements at the end of the collection :o( ), the input being:


<prices>
<price>
<product_id>AAA</product_id>
</price>
<price>
<product_id>BBB</product_id>
</price>
</prices>

osb sample xquery

Since I always need an XQuery skeleton and I am too lazy to run Eclipse, here is one:

xquery version "1.0" encoding "Cp1252";
(:: pragma  parameter="$anyType1" type="xs:anyType" ::)
(:: pragma  type="xs:anyType" ::)

declare namespace xf = "http://tempuri.org/OSB%20Project%201/Sample/";

declare function xf:Sample($anyType1 as element(*))
    as element(*) {
        {count($anyType1)}
};

declare variable $anyType1 as element(*) external;

xf:Sample($anyType1)

Removing empty optional elements erroneously inserted by mapping

if a XQuery mapping, theoretically you should check for existence of an optional element in the source, and only if it exists you create it on the destination:

if ($myvar/customer/shoesize) then
<shoesize>
data($myvar/customer/shoesize)
</shoesize>


If the developer likes to take shortcuts (=is a lazy bum), and doesn't do the "if", you must clean the garbage afterwards.

You can use the action:
delete //*[not(node()) and not(.//@*)] from body

XQuery: how to chain (concat) 2 elements

curly braces will do the job:

let $a := <a>bla</a>
let $b := <b>blu</b>

return { $a, $b }


will return
<a>bla</a>
<b>blu</b>

Throwing exceptions in XQuery - fn:error()

Generating proper error messages is the foundation for a well maintainable application.

One should embed proper assertions in XQuery, using fn:error()

See chapter 3 in here http://www.w3.org/TR/xpath-functions/

A (dummy)example is here:

xquery version "1.0" encoding "Cp1252";
(:: pragma parameter="$anyType1" type="xs:anyType" ::)
(:: pragma type="xs:anyType" ::)

declare namespace xf = "http://tempuri.org/PVTests/raiseTest/";

declare function xf:raiseTest($anyType1 as element(*))
as element(*) {
if ($anyType1/id/text() = 23) then
fn:error(xs:QName('localnameblablabla'), '23 is not a good id')
else
};

declare variable $anyType1 as element(*) external;

xf:raiseTest($anyType1)


and you test it with

23

In OSB only the xs:QName($someString) constructor is supported, and the someString must be the localname (don't use a http://acme.com/errorcode1234 style, it will raise an exception)
although the normal QName constructor supports all possible variations:

http://download.oracle.com/javase/1.5.0/docs/api/javax/xml/namespace/QName.html#QName%28java.lang.String,%20java.lang.String,%20java.lang.String%29


Your error handler will receive a fault:

weblogic.xml.query.exceptions.XQueryUserException: line 1, column 1: localnameblablabla: 23 is not a good id:


<con:fault xmlns:con="http://www.bea.com/wli/sb/context">
<con:errorCode>BEA-382510</con:errorCode>
<con:reason>
OSB Assign action failed updating variable "bla": weblogic.xml.query.exceptions.XQueryUserException: line 1, column 1: localnameblablabla: 23 is not a good id
</con:reason>
<con:location>
<con:node>PipelinePairNode1</con:node>
<con:pipeline>PipelinePairNode1_request</con:pipeline>
<con:stage>stage1</con:stage>
<con:path>request-pipeline</con:path>
</con:location>
</con:fault>

XQuery, convert a dateTime into a date

Stealing almost all the code from http://www.xqueryfunctions.com/xq/fn_year-from-datetime.html I managed to convert a dateTime into a date (dateTimeToDate)...

guys, this is insane...

declare namespace functx = "http://www.functx.com";

declare function functx:repeat-string
( $stringToRepeat as xs:string? ,
$count as xs:integer ) as xs:string {

string-join((for $i in 1 to $count return $stringToRepeat),
'')
} ;

declare function functx:pad-integer-to-length
( $integerToPad as xs:integer? ,
$length as xs:integer ) as xs:string {

if ($length < string-length(string($integerToPad))) then error(xs:QName('functx:Integer_Longer_Than_Length')) else concat (functx:repeat-string( '0',$length - string-length(string($integerToPad))), string($integerToPad)) } ; declare function functx:date ( $year as xs:integer , $month as xs:integer , $day as xs:integer) as xs:date { xs:date( concat( functx:pad-integer-to-length(xs:integer($year),4),'-', functx:pad-integer-to-length(xs:integer($month),2),'-', functx:pad-integer-to-length(xs:integer($day),2))) } ; declare function functx:dateTimeToDate($dateTime1 as xs:dateTime) as xs:date {
functx:date(fn:year-from-dateTime($dateTime1), fn:month-from-dateTime($dateTime1), fn:day-from-dateTime($dateTime1))
};



There is this alternative way, slightly simpler :o)

xs:date(substring-before($dateTime,'T'))

(thanks David for the suggestion, it helps a lot!)

Optional Attributes in XQuery

I have to return some optional attributes in a XQuery.
There are too many of them, so I cannot return a separate XML for each case. Things have to be done dynamically.

This post explains how:

http://forums.oracle.com/forums/thread.jspa?threadID=785203&tstart=1335

It works like a charm! Yet I wish there was a simpler way...in fact there is, just use Groovy XmlBuilder :o)

OSB, side effects of caching Xqueries

One instance of "com.bea.wli.config.derivedcache.DerivedCache" loaded by "sun.misc.Launcher$AppClassLoader @ 0x7812ef450" occupies 1,090,992,792 (72.62%) bytes. The memory is accumulated in one instance of "weblogic.xml.query.xdbcimpl.XQueryPreparedStatementImpl" loaded by "sun.misc.Launcher$AppClassLoader @ 0x7812ef450".


apparently, OSB internally "precompiles" and caches XQuery, which sound pretty reasonable for performance issues.

The side effect is that, if your XQuery is nothing but a huge XML, the format by which this XML is saved in memory is very inefficient.

Our XQuery was simply returning a 10k elements XML, each element being 1K, so a total of 10M on file.

This XQuery ends up taking 1GB of memory, and the performance of my server drops dramatically because very less RAM is available afterwards.

If took a heap dump and Eclipse MAT to discover that.

I think in future we will do a Java Callout returning the content of a file, and converting it to XmlObject with fn-bea:inlinedXML() - that should not put anything in a Cache!

updates: in the custom XPath I read a xml file (. path is relative to user.dir = domain home), using XmlObject.parse(File), and returing XmlObject to OSB.... it works very well without any caching side effect.

This inspires me an interesting pattern: in my Mock Service, I use the unique ID of the request to identify a specific XML file on disk (eg id=408 retrieves file train408.xml). This will enable us to easily serve different data for different IDs.

OSB executing XQuery defined in an external file

Let's admit it, support for reusability of XQuery code is EXTREMELY limited in OSB.
One has to play some trick.

One of them is using custom XPath function - defined in Java - and read and execute dynamically a XQuery, binding parameters dynamically.

Example:

This is the Java code:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

import org.apache.xmlbeans.XmlObject;
import org.apache.xmlbeans.XmlOptions;


static String BASE_DIR = "/path/to/config/osb/";

public static XmlObject trainWithParams(XmlObject[] parameters, String[] name) throws Exception {
String xquery = readFile(BASE_DIR + "train.xq"); 
XmlOptions options = new XmlOptions();
// see http://xmlbeans.apache.org/docs/2.2.0/reference/org/apache/xmlbeans/XmlOptions.html#setXqueryVariables%28java.util.Map%29
Map map = new HashMap();
for (int i = 0; i < name.length; i++) {
   map.put(name[i], parameters[i]);
  }
  options.setXqueryVariables(map);
  return runXQuery(xquery, options)[0];
}


private static XmlObject[] runXQuery(String xquery, XmlOptions options) {
  XmlObject xmlObject = XmlObject.Factory.newInstance();
  XmlObject[] results = xmlObject.execQuery(xquery, options);
  return results;
} 



private static String readFile(String fFileName) throws Exception {
  StringBuilder text = new StringBuilder();
  String NL = System.getProperty("line.separator");
  Scanner scanner = new Scanner(new FileInputStream(fFileName));
  try {
   while (scanner.hasNextLine()){
    text.append(scanner.nextLine() + NL);
   }
  }
  finally{
   scanner.close();
  }
  return text.toString(); 
}

This is the entry in the osb-built-in.xml

trainWithParams
Run a XQuery function returning XmlObject, passing many parameters
http://acme.com
com.acme.osb.XQueryExecutor
org.apache.xmlbeans.XmlObject trainWithParams([Lorg.apache.xmlbeans.XmlObject;, [Ljava.lang.String;)
true
Pipeline
SplitJoin
        

and this is how to invoke it from any XQuery context in OSB:

return acmxk:trainWithParams(($Train), ('Train'))


You can chain multiple parameters:

return acmxk:trainWithParams(($Train, $Bus), ('Train', 'Bus'))



Of course you can pass the Xquery file name in the call itself, by adding an extra parameter to the signature.

I am not sure if the XQuery will be recompiled every time, or it the XQuery engine keeps a cache. This mechanism exists in SQL engines, perhaps it's there also in XQuery engines.

Also I don't know if this compiled statement caching is in place for all XQuery defined in OSB.

Xquery, the need to cast your variables

Today I have wasted 1 hour on this:

the inputs are:

27
Al Quran



1
La Tregua





Mohamed



Levi

my Xquery does:

THIS is WRONG:

let $myAuthorIndex := data($books/book[name="Mohamed"]/authorIndex)
let $myAuthors := $authors/author[$myAuthorIndex]


well, this will not work.... you will get ALL AUTHORS.
The trick is to CAST $myAuthorIndex to an integer, otherwise it will be considered a string and ignored


THIS is correct:

let $myAuthorIndex := xs:int(data($books/book[name="Mohamed"]/authorIndex))
let $myAuthors := $authors/author[$myAuthorIndex]

XQuery to change the local name of an element

returning to the problem of changing operation in a body, that is turning:

<dbac:insertCompany xmlns:dbac="http://com/acme/dbaccess">
  <dbac:company xmlns:java="java:com.acme.dbaccess">
    <java:CreationDate>3</java:CreationDate>
    <java:Id>10</java:Id>
    <java:Name>string</java:Name>
  </dbac:company>
</dbac:insertCompany>


into

<dbac:updateCompany xmlns:dbac="http://com/acme/dbaccess">
  <dbac:company xmlns:java="java:com.acme.dbaccess">
    <java:CreationDate>3</java:CreationDate>
    <java:Id>10</java:Id>
    <java:Name>string</java:Name>
  </dbac:company>
</dbac:updateCompany>


of course one way would be to turn all the payload into a String, do a fn:replace(thestring, oldoperation, newoperation) and then turning it back into a element(*); but it's very unsafe as it replaces everything.

There is a reliable way using this XQuery:


declare namespace functx = "http://www.functx.com";
declare namespace dbac = "http://com/acme/dbaccess";

declare function functx:replace-beginning
  ( $arg as element(*), $name as xs:string,$newName as xs:string)  as element() {
    if (fn:ends-with(string(node-name($arg)), $name) = xs:boolean('true')) then
     element{$newName}{$arg/node()}      
    else
     Error Message
 } ;

declare variable $arg as element(*) external;
declare variable $name as xs:string external;
declare variable $newName as xs:string external;
functx:replace-beginning($arg, $name, $newName)

This is an excellent demonstration of how to instantiate a node (element($newName)} and insert into it portion of the old element.

Excellent XQuery book

http://oreilly.com/catalog/9780596006341

This is the kind of book that you would like to have available for every new technology you want to learn.
Normally men are drifting away when they write computer books; this book, written by a woman, proves the superior communication skills of women.


Examples from this book are available here.

OSB Xquery variables do not show in binding list

check here why:

http://rogervdkimmenade.blogspot.com/2009/07/xquery-use-within-osb-watch-out.html

if you don't actually use the variable in your XQuery, even if it's in the method signature it's not appearing in the binding list.... does this make sense? Perhaps yes, but it's not that intuitive.... at least they should display a warning....

XQuery troubleshooting

you get:
weblogic.xml.query.exceptions.XQueryDynamicException: {err}FORG0005: expected exactly one item, got 2+ items

try this:
using element(*)* instead of element(*)

_________________

OSB: invoke Java from XQuery

http://www.devx.com/tips/Tip/33279

 

Call a Java Method from XQuery

This tip shows you how to develop XQuery queries that may call any other method. The method called in this tip is java.lang.Math.random:

declare namespace m="java:java.lang.Math";

let $r:=m:random()
return $r

This unfortunately doesn't seem to work in OSB 3.0 .... bugger....

I am told that in OSB 11g you can extend XQuery writing custom Java functions... 

extensibility mechanism, where are you... XQuery is waiting it since 2002.... 

I see really a huge community interest in this technology :o(

XQuery and Unit Tests

XQuery can turn into a nightmare, not much during development, but during their maintenance.... XQuery code is very convoluted (XPath expressions can be daunting) and after one week you have coded them I dare you understand what you wrote in the first place. You touch it, you break it.

Hence it's paramount to unit test them. I am looking at the best way of doing it either INSIDE OSB or INSIDE ECLIPSE.

One way could be to follow this approach - INSIDE OSB:

http://blogs.oracle.com/knutvatsendvik/2010/03/unit_testing_framework_for_xquery.html

The utility is very well thought, especially impressive if the XMLDiffXQ utility

As for invoking directly XQuery from Java, here are the xquery related jars in OSB:

   ¦  C:\bea103osb\modules\                                                                                       ¦

   ¦   22-04-10 13:52¦        54443¦   A      ¦com.bea.core.xquery.beaxmlbeans-interop_1.2.1.0.jar                ¦

   ¦   22-04-10 13:52¦        55472¦   A      ¦com.bea.core.xquery.xmlbeans-interop_1.2.1.0.jar                   ¦

   ¦   22-04-10 13:52¦      4563307¦   A      ¦com.bea.core.xquery_1.2.1.0.jar                                    ¦

   ¦ ------------------------------------------------------------------------------------------------------------ ¦

   ¦  C:\bea103osb\modules\features\                                                                              ¦

   ¦   22-04-10 13:52¦          517¦   A      ¦weblogic.server.modules.xquery_10.3.0.0.jar                        ¦

   ¦ ------------------------------------------------------------------------------------------------------------ ¦

   ¦  C:\bea103osb\osb_10.3\eclipse\plugins\com.bea.alsb.xquery.xmlbeans-interop_1.0.200\lib\                     ¦

   ¦   22-04-10 14:22¦        54443¦   A      ¦com.bea.core.xquery.beaxmlbeans-interop_1.2.1.0.jar                ¦

   ¦   22-04-10 14:22¦        55472¦   A      ¦com.bea.core.xquery.xmlbeans-interop_1.2.1.0.jar                   ¦

   ¦ ------------------------------------------------------------------------------------------------------------ ¦

   ¦  C:\bea103osb\osb_10.3\eclipse\plugins\com.bea.alsb.xquery_1.0.200\lib\                                      ¦

   ¦   22-04-10 14:22¦      4563307¦   A      ¦com.bea.core.xquery_1.2.1.0.jar                                    ¦

   ¦ ------------------------------------------------------------------------------------------------------------ ¦

   ¦  C:\bea103osb\tools\eclipse_pkgs\2.0\pkgs\eclipse\plugins\com.bea.wli.ide.xquery.core_10.3.0\                ¦

   ¦   22-04-10 14:05¦       150403¦   A      ¦xquery-core.jar                                                    ¦

   ¦ ------------------------------------------------------------------------------------------------------------ ¦

   ¦  C:\bea103osb\tools\eclipse_pkgs\2.0\pkgs\eclipse\plugins\com.bea.wli.ide.xquery.ui_10.3.0\                  ¦

   ¦   22-04-10 14:05¦       148611¦   A      ¦xquery-ui.jar                                                      ¦

Here http://biemond.blogspot.com/2008/11/using-xquery-in-jdeveloper-11g-and.html is an example of how to execute XQuery code from Java... but it requires JDeveloper!

Here http://wordpress.transentia.com.au/wordpress/2010/11/20/unit-testing-xquery-using-osbs-api-2/  is someone who seems to have drunk the bitter cup to its last sip and managed to implement Unit Testing for OSB Xqueries..... Kudos!

Parsing a XSD schema file

I am looking for the Philosopher's stone turning XSDs in XQuery mapping . I want to invent a tool to generate for me XQuery Mapping code in an intelligent, introspective, rule-driven way.

This is emp.xsd

<?xml version="1.0" encoding="UTF-8"?>
 <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="java:com.testws.data" xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:complexType name="Employee">
     <xs:sequence>
       <xs:element minOccurs="1" name="Age" nillable="false" type="xs:int"/>
       <xs:element minOccurs="1" name="Name" nillable="true" type="xs:string"/>
     </xs:sequence>
   </xs:complexType>
 </xs:schema>

this is my code

        XmlObject xmlObject = XmlObject.Factory.parse(new File("emp.xsd"));
        xmlObject.dump();


This is what I get:

  ROOT (USER) *:R:<cur>[0] <mark>[0] (DocumentXobj)
    ELEM xs:schema@http://www.w3.org/2001/XMLSchema (ElementXobj)
      ATTR attributeFormDefault Value( "unqualified" ) (AttrXobj)
      ATTR elementFormDefault Value( "qualified" ) (AttrXobj)
      ATTR targetNamespace Value( "java:com.testws.data" ) (AttrXobj)
      ATTR xmlns:xs@http://www.w3.org/2000/xmlns/ Value( "http://www.w3.org/2001/XMLSchema" ) After( "\n   " ) (AttrXobj)
      ELEM xs:complexType@http://www.w3.org/2001/XMLSchema After( "\n " ) (ElementXobj)
        ATTR name Value( "Employee" ) After( "\n     " ) (AttrXobj)
        ELEM xs:sequence@http://www.w3.org/2001/XMLSchema Value( "\n       " ) After( "\n   " ) (ElementXobj)
          ELEM xs:element@http://www.w3.org/2001/XMLSchema After( "\n       " ) (ElementXobj)
            ATTR minOccurs Value( "1" ) (AttrXobj)
            ATTR name Value( "Age" ) (AttrXobj)
            ATTR nillable Value( "false" ) (AttrXobj)
            ATTR type Value( "xs:int" ) (AttrXobj)
          ELEM xs:element@http://www.w3.org/2001/XMLSchema After( "\n     " ) (ElementXobj)
            ATTR minOccurs Value( "1" ) (AttrXobj)
            ATTR name Value( "Name" ) (AttrXobj)
            ATTR nillable Value( "true" ) (AttrXobj)
            ATTR type Value( "xs:string" ) (AttrXobj)



to be continued.... I suspect Groovy has a far better support for XML parsing than native Java...

XQuery tutorial: frequently used stuff

See here http://en.wikibooks.org/wiki/XQuery  for a wealth of excellent examples.

Also http://www.xqueryfunctions.com/xq/  is wonderful.

XQuery is a terrible pain in the knee, but if you get organized it's only a matter of copying/pasting/hacking....

___________________

Variable substitution in a text:

xquery version "1.0" encoding "Cp1252";
(:: pragma  type="xs:anyType" ::)

declare namespace xf = "http://tempuri.org/PVOSBProject1/XQExceptionHandling/";

declare function xf:XQExceptionHandling($thefault as xs:string)
as element(*) {
<ws:handleException xmlns:ws="http://com/pierre/ws">
    <ws:fault>{$thefault}</ws:fault>
</ws:handleException>   
};


declare variable $thefault as xs:string external;

xf:XQExceptionHandling($thefault)

___________________

if you want to pass any xml, use
declare variable $thefault as element(*) external;


______


if you callout a service expecting a String , you cannot pass an XML.... first you must fn-bea:serialize($thevariable)

the function fn:string($body) will somehow generate a toString() of your XML
_____________

Assume this is the $body:


<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <dbac:insertCompany xmlns:dbac="http://com/acme/dbaccess">
    <dbac:company xmlns:java="java:com.acme.dbaccess">
      <java:CreationDate>3</java:CreationDate>
      <java:Id>2</java:Id>
      <java:Name>Pluto</java:Name>
    </dbac:company>
  </dbac:insertCompany>
</soapenv:Body>


$body/.  means all the body... equivalent to $body

$body/dbac:company returns this:

<dbac:company      xmlns:java="java:com.acme.dbaccess" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"  xmlns:dbac="http://com/acme/dbaccess">
    <java:CreationDate>3</java:CreationDate>
    <java:Id>2</java:Id>
    <java:Name>Pluto</java:Name>
    </dbac:company>


$body/*/node()  also returns

<dbac:company      xmlns:java="java:com.acme.dbaccess" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"  xmlns:dbac="http://com/acme/dbaccess">
    <java:CreationDate>3</java:CreationDate>
    <java:Id>2</java:Id>
    <java:Name>Pluto</java:Name>
    </dbac:company>

(be careful: $body/node() is different from $body/*/node() )

 $operation will contain insertCompany

 $body/node() will return
  <dbac:insertCompany xmlns:dbac="http://com/acme/dbaccess">
    <dbac:company xmlns:java="java:com.acme.dbaccess">
      <java:CreationDate>3</java:CreationDate>
      <java:Id>2</java:Id>
      <java:Name>Pluto</java:Name>
    </dbac:company>
  </dbac:insertCompany>

use the condition fn:compare($a, $b) = 0 to test equality of 2 strings

fn:name($body) will return  dbac:insertCompany

fn:local-name($body) will return  insertCompany

fn:node-name($body): {http://com/acme/dbaccess}insertCompany

___________

XQuery: converting nodes in a CSV list

I have this:

<getLocationsByLocationIds>
    <string>string_1</string>
    <string>string_2</string>
</getLocationsByLocationIds>


and I want to convert it into this:
string_1,string_2


use this:

fn:string-join( $body/getLocationsByLocationIds/string, ',')

___________