Thursday, July 24, 2008

Using XQuery with PeopleSoft

Like XSLT 1.0, XQuery provides a method for transforming XML. Besides the obvious differences in syntax, XQuery provides additional functionality that doesn't exist in the XSLT 1.0 specification. For example, XQuery not only provides the ability to transform a single XML document into a single output document, like XSLT 1.0, but also adds the ability to merge and/or join multiple input documents into a single result document. I'll let you look up the rest of the differences between the 2 languages. I'm not going to say either is better. They are different and each has its place. The main question I want to answer is, "How can I use XQuery with PeopleSoft?"

To use XQuery from PeopleSoft, you will need to download an XQuery library, install it, and configure your PeopleSoft app server to use it. The example code that follows uses the Saxon XQuery processor. Which version of Saxon you download and how you configure your app server to support Saxon will depend on which version of PeopleTools you are using. This difference is the result of changes made to the Java JAXP API between Java 1.4.2 (PT 8.48 and earlier) and Java 1.5 (PT 8.49). Below, you will find separate configuration sections for the Java 1.4.2 PeopleTools versions and the Java 1.5 PeopleTools version. To find out which Java version your app server uses, execute the following command:

%PS_HOME%\jre\bin\java.exe -version.

When you add the Saxon jars to your classpath, you will be adding a second implementation of the JAXP interfaces to your app server's Java runtime environment. PeopleSoft uses the Apache (Xalan/Xerces) JAXP implementation. Both of these implementations, Saxon and Apache, will register themselves as the default JAXP factory implementation. To ensure that PeopleSoft works correctly after installing the Saxon jars, you need to explicitly set the default JAXP implementation. There are 2 ways to do this: the jaxp.properties file or JVM system properties. In this example, I will give the steps for modifying your app server's JVM system properties, ignoring the jaxp.properties alternative.

To ensure that you configure our app server correctly, you need a way to determine your current JAXP settings. I wrote the following PeopleCode to assist you in configuring your JAXP settings. When run from an IScript, this code will give you the JAXP settings used by your online app server, formatted so that you can copy and paste it into your psappsrv.cfg file, as described in a later step. If you will be using XQuery in your process scheduler server, then you can replace the text %Response.WriteLine with MessageBox, and run this same PeopleCode from an AppEngine program. To run this code online, you will need to create a WEBLIB and IScript and paste this code into your record field PeopleCode:

Function IScript_GetJAXPSystemProperties()
Local string &XPathFactorySetting = "";
Local string &XPathFactoryName = "";

try
&XPathFactoryName = GetJavaClass("javax.xml.xpath.XPathFactory").newInstance().getClass().getName();
If (All(&XPathFactoryName)) Then
&XPathFactorySetting = " -Djavax.xml.xpath.XPathFactory=" | &XPathFactoryName | " -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=" | &XPathFactoryName;
End-If;
catch Exception &e1
end-try;

%Response.SetContentType("text/plain");
%Response.WriteLine("-Djavax.xml.transform.TransformerFactory=" | GetJavaClass("javax.xml.transform.TransformerFactory").newInstance().getClass().getName() | &XPathFactorySetting);
End-Function;

After creating your IScript, you can run this code from a URL similar to:

http://<server>:<port>/psc/<site>/EMPLOYEE/<node>/s/WEBLIB_CSS_SAXN.ISCRIPT1.FieldFormula.IScript_GetJAXPSystemProperties

Just replace the parts in < > with your site specific values.

Running this code after installing Saxon should give you the exact same result. If it doesn't, then something in the Saxon jars is overriding the PeopleSoft delivered value. Check your Java VM options to ensure that you have them set correctly. Besides your initial run prior to installing Saxon, I suggest you run this again after you install the Saxon jars and BEFORE you update the psappsrv.cfg file to see how the Factory class implementations change with the presence of the Saxon jars. Then, after you modify your psappsrv.cfg file, you can be sure that the JVM is set correctly.

PeopleTools version specific installation steps:

PT 8.48/Java 1.4.2

  1. Download Saxon version 8.9.04 from the Saxon SourceForge file repository
  2. Extract saxon8.jar, saxon8-xqj.jar, and saxon8-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
  3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl.
  4. Restart your app server

PT 8.49/Java 1.5

  1. Download Saxon version 9.1.0.1 from the Saxon SourceForge file repository
  2. Extract saxon9.jar and saxon9-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
  3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl -Djavax.xml.xpath.XPathFactory=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl.
  4. Restart your app server

Downloading jars and placing them in the class path is standard practice when adding new Java libraries to a PeopleSoft implementation. Step 3 above, however, is unique. By specifically setting the JAXP system properties in the psappsrv.cfg file, we force the JVM to use the correct JAXP factories regardless of the order in which the JVM loads our Saxon/Apache jar files.

The following PeopleCode demonstrates how to execute an XQuery from PeopleCode. You will notice that the following PeopleCode references an HTML object named RSS_XQ. You can download the HTML for this HTML object (really, XQuery source, not HTML) from my online repository: rss2_html_obj.xq

Function ExecXQuery(&xquery As string) Returns string
Local JavaObject &jConfig = CreateJavaObject("net.sf.saxon.Configuration");
Local JavaObject &jClass = GetJavaClass("java.lang.Class");

Local JavaObject &jStaticContext = CreateJavaObject("net.sf.saxon.query.StaticQueryContext", &jConfig);

rem ** use Java reflection to call the compile method;
rem Local JavaObject &jExp = &jStaticContext.compileQuery(GetHTMLText(HTML.RSS_NYTIMES_BUSINESS_XQ));
Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));
Local JavaObject &jCompileMethod = &jStaticContext.getClass().getDeclaredMethod("compileQuery", &jCompileArgTypes);
Local JavaObject &jExp = &jCompileMethod.invoke(&jStaticContext, CreateJavaObject("java.lang.Object[]", &xquery));

Local JavaObject &jOutputProperties = CreateJavaObject("java.util.Properties");
rem ** set any output properties like encoding, method, etc;

Local JavaObject &jResultWriter = CreateJavaObject("java.io.StringWriter");
Local JavaObject &jStreamResult = CreateJavaObject("javax.xml.transform.stream.StreamResult");
Local JavaObject &jDynamicContext = CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig);

&jStreamResult.setWriter(&jResultWriter);

rem PeopleCode engine thinks &jExp is java.lang.Object, not net.sf.saxon.query.XQueryExpression so we need to continue to use reflection;
rem &jExp.run(CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig), &jStreamResult, &jOutputProperties);
Local JavaObject &jExpressionRunMethod = &jExp.getClass().getDeclaredMethod("run", CreateJavaObject("java.lang.Class[]", &jDynamicContext.getClass(), &jClass.forName("javax.xml.transform.Result"), &jOutputProperties.getClass()));
&jExpressionRunMethod.invoke(&jExp, CreateJavaObject("java.lang.Object[]", &jDynamicContext, &jStreamResult, &jOutputProperties));

Return &jResultWriter.toString();
End-Function;

Function IScript_ExecXQuery()
%Response.Write(ExecXQuery(GetHTMLText(HTML.RSS_XQ, "http://www.nytimes.com/services/xml/rss/nyt/Business.xml")));
End-Function;

As you can see, I wrapped the XQuery transformation in a function called ExecXQuery. The Saxon XQuery classes use overloads that can't be interpreted by the PeopleCode interpreter. To work around this, I had to use some Java reflection. Chris Heller did an excellent job of explaining Java reflection and PeopleCode in his post Java and PeopleCode Tips and Tricks - Part 2.

In this PeopleCode example, I've only scratched the surface of what you can do with Saxon and XQuery. Saxon includes methods for running queries to return lists, methods for dynamically setting input documents, etc. I'll let you investigate the power of Saxon. If you just want to execute xqueries as described in this post, then I suggest you add the ExecXQuery function to a FUNCLIB. Once you have the function in a FUNCLIB, you can call it from Integration Broker PeopleCode transformations. Likewise, you can use it to create a custom Pagelet Wizard data source or transformer. Using a delivered data source like HTML, you could place an XQuery in the HTML text box, and then apply an XQuery display type to that HTML data source to execute the XQuery. Creating a Pagelet Wizard XQuery transformer would allow you to execute XQueries against data sources like content management, news publications, integration broker, PeopleSoft queries, HTML, etc. Unlike an XSL transform, using XQuery, you could merge content from a news publication with other online documents.

If you plan to use other Saxon features, then you may want to create an app package with app classes to encapsulate PeopleCode interfaces to Saxon's XQuery Java API.

Oracle OpenWorld, 2008

Are you ready for another outstanding OpenWorld conference? I am! If you are interested in digging deeper into PeopleTools and seeing some good PeopleTools demonstrations, sign up for my PeopleTools Advanced Tips and Techniques session. In this session, I'll be squashing the myth, "You can't do that with PeopleTools." This session is currently slated for Tuesday from 5:00pm - 6:00pm. Likewise, if you are interested in learning more about the best Enterprise class portal ever created, put one of these PeopleSoft Enterprise Portal sessions on your agenda (The IntraSee presentation, S300269, is outstanding!).

Do you have a specific PeopleTools question for me? Stop by the PeopleTools user interface demo pod in the demo grounds. I'll be working regular shifts at that demo pod throughout the conference.

Blogger Bots Marked my Blog as Spam

Sorry I haven't posted for a while. The blogger.com bots marked this blog as a spam blog. Blogger's help file says, "spam blogs... can be recognized by their irrelevant, repetitive, or nonsensical text." That must be it. Anyway, I apologize for any page I took out of service during this downtime. While out of service, I made the mistake of editing a page. Since blogger.com wouldn't let me publish that page, I had to save it as a draft. By saving it as a draft, the page was pulled from my site. Go figure.

While my blog was locked, I wrote up a few posts and saved them as drafts. To save as drafts, I had to enter those "word verification" characters. Those are fun... On several occasions, I had to try 3+ times before I finally got a character combination I could enter successfully.