Using XQuery in JDeveloper 11g and Workshop

With JDeveloper 11g you can use XQuery in your java code and test it with the XQuery runner of JDeveloper. But the only thing missing is a XQuery Designer. When Oracle bought Bea they also bought Workshop for Weblogic. This Workshop application is eclipse plugin which has a very nice XQuery designer. So let's combine these Oracle tools and make some awesome transformations.
First we create a new application / project in JDeveloper 11g with the following libraries.

Create a new xquery file in the project.

Start Workshop for Weblogic where we will create a new project and add a new XQuery Transformation.

Define the input of the XQuery. This can be a xml schema or something else. In my case I will use a xml schema. Workshop has now created a new input variable called data_message1.

My output is also a xml schema

Workshop provides a Expression functions window with all the XQuery functions you can use. Just drag a function to the target expression.

Here you have a overview of the XQuery mappings. Data is a anyType element which I map to the output schema

With the Workshop application you can also test the XQuery file by providing a input xml.

Now we can go back to JDeveloper where we will use this workshop XQuery and test this XQuery file. This is the original XQuery file from Workshop.

(:: pragma bea:global-element-parameter parameter="$data_message1" element="ns2:data-message" location="../xsd/mhs/main_mhs.xsd" ::)
(:: pragma bea:global-element-return element="ns1:data" location="../xsd/mhs/main_delfor.xsd" ::)

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
  as element(ns1:data) {
      <ns1:data RUNID = "{ xs:integer(data($data_message1/ns2:runid)) }"
                SENDER = "{ data($data_message1/ns2:sender) }"
                RECEIVER = "{ data($data_message1/ns2:recipient) }"
                TIMESTAMP = "{ data($data_message1/ns2:timestamp) }">
          <ns0:DELFOR>{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }</ns0:DELFOR>
      </ns1:data>
};

declare variable $data_message1 as element(ns2:data-message) external;

xf:main_delfor($data_message1)

We have to change this XQuery file so it works in JDeveloper. We have to import the input schema else XQuery won't recognize this element(ns2:data-message) definition. To test the XQuery with the XQuery runner I need to change the external $data_message1 variable. This how the JDeveloper version looks like

import schema default element namespace "http://tennet.org/mhs/queue" at "main_mhs.xsd";

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
  as element(ns1:data) {
      <ns1:data RUNID = "{ xs:integer(data($data_message1/ns2:runid)) }"
                SENDER = "{ data($data_message1/ns2:sender) }"
                RECEIVER = "{ data($data_message1/ns2:recipient) }"
                TIMESTAMP = "{ data($data_message1/ns2:timestamp) }">
          <ns0:DELFOR>{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }</ns0:DELFOR>
      </ns1:data>
};

declare variable $data_message2  := doc('delfor2.xml');
declare variable $data_message1  as element(ns2:data-message)  := $data_message2/data-message;

xf:main_delfor($data_message1)

In JDeveloper we can select the XQuery file and press run to see the output.

Off course we can run the xquery file in java too. We only have to use this class and make $data_message2 a external variable in the XQuery file

package nl.ordina.xquery;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;

import javax.xml.namespace.QName;

import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLParseException;
import oracle.xml.xqxp.datamodel.XMLItem;
import oracle.xml.xqxp.datamodel.XMLSequence;
import oracle.xquery.PreparedXQuery;
import oracle.xquery.XQueryContext;
import oracle.xquery.exec.Utils;
import org.xml.sax.SAXException;


public class TestXQuery {

   public TestXQuery() {
    try {

         XQueryContext ctx = new XQueryContext();
         Reader strm = new FileReader("main_delfor2.xq");
         PreparedXQuery xquery = ctx.prepareXQuery(strm);
        
         DOMParser prs = new DOMParser();
         InputStream is = Utils.getStream("delfor2.xml");
         prs.parse(is);
         XMLDocument doc = prs.getDocument();

         xquery.setNode( new QName("data_message2"), doc);

         XMLSequence seq = xquery.executeQuery();
         while (seq.next()) {
           XMLItem item = seq.getCurrentItem();
             item.getNode().print(System.out);
         }
       } catch (FileNotFoundException e) {
          e.printStackTrace();
       } catch (IOException e) {
           e.printStackTrace();
       } catch (XMLParseException e) {
           e.printStackTrace();
       } catch (SAXException e) {
           e.printStackTrace();
       }
   }

   public static void main(String[] args) {
       TestXQuery testXQuery = new TestXQuery();
   }
}

and this is the xquery I used for the java example

import schema default element namespace "http://tennet.org/mhs/queue" at "main_mhs.xsd";

declare namespace ns2 = "http://tennet.org/mhs/queue";
declare namespace ns1 = "http://tennet.org/mhs";
declare namespace ns0 = "http://tennet.org/mhs/delfor";
declare namespace xf = "http://tempuri.org/MHS/xquery/main_delfor/";

declare function xf:main_delfor($data_message1 as element(ns2:data-message))
  as element(ns1:data) {
      <ns1:data RUNID = "{ xs:integer(data($data_message1/ns2:runid)) }"
                SENDER = "{ data($data_message1/ns2:sender) }"
                RECEIVER = "{ data($data_message1/ns2:recipient) }"
                TIMESTAMP = "{ data($data_message1/ns2:timestamp) }">
          <ns0:DELFOR>{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }</ns0:DELFOR>
      </ns1:data>
};

declare variable $data_message2  external;
declare variable $data_message1  as element(ns2:data-message)  := $data_message2/data-message;

xf:main_delfor($data_message1)

Here you can download the Jdeveloper project I used in this blog.