Pages

Friday, November 28, 2008

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) {

{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }

};

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) {

{ $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/@* , $data_message1/ns2:contentlist/ns2:content[1]/ns2:data/node() }

};

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.

10 comments:

  1. Hi Edwin,
    I used the same project shared by you and whle running the XQuery its working fine i am getting the response.
    But while running the TestXQuery.java class, i am getting the following exception.

    Exception in thread "main" oracle.xquery.XQException: XQST0059: It is a static error if an implementation is unable to process a schema or module import by finding a schema or module with the specified target namespace.
    at oracle.xquery.PreparedXQuery.(PreparedXQuery.java:202)
    at oracle.xquery.PreparedXQuery.(PreparedXQuery.java:147)
    at oracle.xquery.PreparedXQuery.(PreparedXQuery.java:211)
    at oracle.xquery.XQueryContext.prepareXQuery(XQueryContext.java:464)
    at nl.ordina.xquery.TestXQuery.(TestXQuery.java:30)
    at nl.ordina.xquery.TestXQuery.main(TestXQuery.java:56)

    Can you please tell me how to resolve this?

    Thanks
    Deepthi.

    ReplyDelete
  2. I have the same problem. When i run in jdeveloper works fine, when i run in netbeans i have the same error..

    ReplyDelete
  3. Hi,

    with this jar it works fine oracle_common\modules\oracle.xdk_11.1.0\xquery.jar

    I think it has something to do with the xmlparser of oracle.

    thanks

    ReplyDelete
  4. Hi,

    Stylus Studio allows you to just drag a relational db onto their canvas and create a XQuery that parses an xml and just inserts this into a relational database(Oracle).
    I am looking for something similar: create a java app to be used in or in conjunction with a oracle db and feedd it a XQuery file and a xmlsource so it parses the xml and pushes it in the db. Is this possible without buying the SS product ? Or is the sql-insert they use proprietary ?

    ReplyDelete
  5. priceless! Thanks Edwin!
    I have discovered very nice features of the Eclipse plugin I was not aware of...

    yet I feel the plugin is not very stable, many times you must close and reopen to get rid of errors

    ReplyDelete
  6. Hi

    I was inspired by your example to write tests.
    But I'm experiencing difficulties executing XQuery with functions from fn-bea namespace e.g. fn-bea:date-from-dateTime($endDate).
    Do you have any idea how to make it work? Or a direction to look at. Because I don't :(

    Thanks in advance.

    ReplyDelete
    Replies
    1. Hi,

      What IDE are you using, Don't think this will work in jdeveloper.

      thanks

      Delete
  7. Hi Edwin,
    Thanks for useful post,every time we learn new thing by reading your post.This is post also very good,I follow the steps I am also able to call the xquery in java API(sample program)but for my xquery multiple source variables are required,how to pass the multiple variables as source variables and also bea function are not working in JAVA API i thing it requires jar,could to please look into this how to resolve this issue.

    Thanks
    Mani

    ReplyDelete
  8. For use it in servlet on application server you need
    1. Replace first line in xquery file
    import schema default element namespace "something" at "location.xsd";
    with
    declare default element namespace "something";

    2. Replace
    Reader strm = new FileReader("main_delfor2.xq");
    with
    InputStream resourceContent = servletContext.getResourceAsStream("/WEB-INF/classes/nl/ordina/xquery/main_delfor2.xq");
    Reader strm = new InputStreamReader(resourceContent);

    ReplyDelete