h5. Introduction The ability to search the payload of messages was added to XI with SP15's TREX integration. However, searching with XML standards such as XPath or XQuery is not supported, meaning that advanced queries are still hard at best and impossible at worst. One possible workaround is to store a copy of select messages in an external database, specifically a native XML database. The messages can then be searched using the database query tools. This blog entry describes an experiment with this approach. h5. A bird's eye view of my setup For the experiment, I installed the eXist database (http://exist.sourceforge.net/), which is one of a handful of open source, native XML databases available today. It has great documentation and it's very easy to get up and running. I already had an XI scenario where XML purchase orders were delivered to an R/3 system as IDocs. I added an extra receiver to the Receiver Determination, so that every purchase order would be delivered to the eXist database as well as to R/3. The purchase orders are wrapped in an envelope and written to the file system, where a Java application picks them up and stores them in the database.
h5. Wrapping the messages Being able to store and query the payload of each message is great, but we'll need metadata about the message (ID, sender interface, receiver interface etc) as well. The following XSLT stylesheet wraps any message in an envelope containing both metadata and payload. The envelope is what we're storing in eXist. DISCLAIMER : Please note that the following source code is provided for educational purposes +only+. It is +not+ intended for production use. h5. Storing documents in eXist There are a few different APIs that you can use with eXist. I chose to use the {code:html}XML:DB{code} API to store documents in the database. XML:DB is a portable API that is to native XML databases what JDBC is to relational databases. The following program, a stand-alone Java application, stores all XML documents, located in a specified directory, in an eXist collection. A collection is a set of stored XML documents, that you might compare to a table in a relational database. The interfaces of interest in the code are Database, Collection and XMLResource. Please refer to the XML:DB {code:html}documentation{code} for further details about these and other XML:DB types. DISCLAIMER : Please note that the following source code is provided for educational purposes +only+. It is +not+ intended for production use. AddPOsToDb.java package dk.applicon.xi.xmlstore;import java.io.File;import java.io.FileFilter;import org.xmldb.api.DatabaseManager;import org.xmldb.api.base.Collection;import org.xmldb.api.base.Database;import org.xmldb.api.modules.XMLResource;public class AddPOsToDb { private static final String DOC_EXTENSION = ".xml"; private static final String EXIST_DB_DRIVER = "org.exist.xmldb.DatabaseImpl"; private static final String URI = "xmldb:exist://localhost:8080/exist/xmlrpc"; // Assuming a locally running database. private static final String COLLECTION = "/db/xipurchaseorders"; public static void main(String args[]) { if(args.length != 1) { System.out.println("Usage: java dk.applicon.xi.xmlstore.AddPOsToDb doc_dir"); System.exit(1); } File docDir = new File(args[0]); try { // Load driver and create a Database instance. Class driverClass = Class.forName(EXIST_DB_DRIVER); Database db = (Database)driverClass.newInstance(); DatabaseManager.registerDatabase(db); // Get the Purchase Order collection (assumed to // already exist). Collection col = DatabaseManager.getCollection(URI + COLLECTION); // Get an array of the documents to be stored. File[] toStore = docDir.listFiles(new FileFilter() { public boolean accept(File f) { return f.getName().endsWith(DOC_EXTENSION); } }); // Create an XMLResource for each document and // store it in the collection. for (int i = 0; i < toStore.length; i++) { File f = toStore[i]; XMLResource doc = (XMLResource)col.createResource(col.createId(), "XMLResource"); doc.setContent(f); col.storeResource(doc); } } catch (Exception e) { throw new RuntimeException(e); } }} h5. Querying the stored documents Here's the contents of the collection after a few purchase orders have been sent through XI to R/3:
For my example query, I'm looking for purchase orders that satisfy the following conditions: ** The PO must contain 3 order lines ** ** The PO must contain an order line where the SKU is 'ABC-123' and the quantity is less than 100 ** ** The PO must contain an order line where the SKU is 'DEF-456' and the quantity is 1 ** (Yes, I pulled those conditions out of thin air, but please bear with me for now 
The following XQuery program returns the XI message IDs of the purchase orders we're looking for: declare namespace po="http://applicon.dk/PO"; for $d in collection("/db/xipurchaseorders")/msgenvelope where count($d/payload//po:item) eq 3 and exists($d/payload//po:item[@partNum = 'ABC-123' and po:quantity lt 100]) and exists($d/payload//po:item[@partNum = 'DEF-456' and po:quantity eq 1]) return $d/metadata/id Notice how the payload elements belong to a namespace while the envelope and metadata elements do not. Here's a screenshot of the program running in eXist's GUI query interface: 
h5. Wrapping the messages Being able to store and query the payload of each message is great, but we'll need metadata about the message (ID, sender interface, receiver interface etc) as well. The following XSLT stylesheet wraps any message in an envelope containing both metadata and payload. The envelope is what we're storing in eXist. DISCLAIMER : Please note that the following source code is provided for educational purposes +only+. It is +not+ intended for production use. h5. Storing documents in eXist There are a few different APIs that you can use with eXist. I chose to use the {code:html}XML:DB{code} API to store documents in the database. XML:DB is a portable API that is to native XML databases what JDBC is to relational databases. The following program, a stand-alone Java application, stores all XML documents, located in a specified directory, in an eXist collection. A collection is a set of stored XML documents, that you might compare to a table in a relational database. The interfaces of interest in the code are Database, Collection and XMLResource. Please refer to the XML:DB {code:html}documentation{code} for further details about these and other XML:DB types. DISCLAIMER : Please note that the following source code is provided for educational purposes +only+. It is +not+ intended for production use. AddPOsToDb.java package dk.applicon.xi.xmlstore;import java.io.File;import java.io.FileFilter;import org.xmldb.api.DatabaseManager;import org.xmldb.api.base.Collection;import org.xmldb.api.base.Database;import org.xmldb.api.modules.XMLResource;public class AddPOsToDb { private static final String DOC_EXTENSION = ".xml"; private static final String EXIST_DB_DRIVER = "org.exist.xmldb.DatabaseImpl"; private static final String URI = "xmldb:exist://localhost:8080/exist/xmlrpc"; // Assuming a locally running database. private static final String COLLECTION = "/db/xipurchaseorders"; public static void main(String args[]) { if(args.length != 1) { System.out.println("Usage: java dk.applicon.xi.xmlstore.AddPOsToDb doc_dir"); System.exit(1); } File docDir = new File(args[0]); try { // Load driver and create a Database instance. Class driverClass = Class.forName(EXIST_DB_DRIVER); Database db = (Database)driverClass.newInstance(); DatabaseManager.registerDatabase(db); // Get the Purchase Order collection (assumed to // already exist). Collection col = DatabaseManager.getCollection(URI + COLLECTION); // Get an array of the documents to be stored. File[] toStore = docDir.listFiles(new FileFilter() { public boolean accept(File f) { return f.getName().endsWith(DOC_EXTENSION); } }); // Create an XMLResource for each document and // store it in the collection. for (int i = 0; i < toStore.length; i++) { File f = toStore[i]; XMLResource doc = (XMLResource)col.createResource(col.createId(), "XMLResource"); doc.setContent(f); col.storeResource(doc); } } catch (Exception e) { throw new RuntimeException(e); } }} h5. Querying the stored documents Here's the contents of the collection after a few purchase orders have been sent through XI to R/3:
For my example query, I'm looking for purchase orders that satisfy the following conditions: ** The PO must contain 3 order lines ** ** The PO must contain an order line where the SKU is 'ABC-123' and the quantity is less than 100 ** ** The PO must contain an order line where the SKU is 'DEF-456' and the quantity is 1 ** (Yes, I pulled those conditions out of thin air, but please bear with me for now The following XQuery program returns the XI message IDs of the purchase orders we're looking for: declare namespace po="http://applicon.dk/PO"; for $d in collection("/db/xipurchaseorders")/msgenvelope where count($d/payload//po:item) eq 3 and exists($d/payload//po:item[@partNum = 'ABC-123' and po:quantity lt 100]) and exists($d/payload//po:item[@partNum = 'DEF-456' and po:quantity eq 1]) return $d/metadata/id Notice how the payload elements belong to a namespace while the envelope and metadata elements do not. Here's a screenshot of the program running in eXist's GUI query interface: | User | Count |
|---|---|
| 524 | |
| 263 | |
| 238 | |
| 234 | |
| 167 | |
| 157 | |
| 152 |
