The ReQuIrEmEnT
The business requirement is to read data from a database table of an external system, map and route in XI, and send to an SAP system (as IDoc, but this is really meaningless).
As you can see, what is done it's simply to explode the content of the xmlfield node, so that in the mapping that takes place afterwards in the XI Mapping Runtime, nodes under xmlfield can be treated as real nodes rather than a single string, which is not convenient.
The SoLuTiOn
An advanced mapping program (such as Java, ABAP or XSLT) could have been used to achieve the result, but unfortunately that means writing a different mapping program for each interface, as both source document (basically database table fields) and target document could have different requirements, and it's hard to pass easily maintainable parameters to a mapping program.
The solution is thus packaged into an additional module, developed as Enteprise Java Bean, that is added in the module chain of the JDBC Sender Adapter – before the standard localejbs/CallSapAdapter - and which support its own parameters.
(Once again, I won't cover here the details of writing adapter modules... I've already given detailed info in other blogs o'mine, and more you'll find in this wonderful SAP howto).
Take the code below and put it in an EJB (see recommendations above). Sorry for some Italian here and there... Exercise for you: can you guess the meaning? 😛
package com.guarneri.xi.afw.modules;
// Standard ejb imports
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.ejb.CreateException;
// XI specific imports
import com.sap.aii.af.mp.module.ModuleContext;
import com.sap.aii.af.mp.module.ModuleData;
import com.sap.aii.af.mp.module.ModuleException;
import com.sap.aii.af.ra.ms.api.*;
import com.sap.aii.af.service.auditlog.*;
import com.sap.engine.lib.xml.util.DOMSerializer;
// XML manipulation imports
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.*;
// Other imports
import java.util.Date;
import java.util.Hashtable;
import java.io.*;
import org.apache.commons.lang.StringEscapeUtils;
/**
@ejbHome <{com.guarneri.xi.afw.modules.JDBCxmlHome}>
@ejbLocal <{com.guarneri.xi.afw.modules.JDBCxmlLocal}>
@ejbLocalHome <{com.guarneri.xi.afw.modules.JDBCxmlLocalHome}>
@ejbRemote <{com.guarneri.xi.afw.modules.JDBCxml}>
@stateless
@transactionType Container
*/
// Module name to use in the adapter:
// localejbs/sap.com/com.guarneri.xi.afw.modules/JDBCxmlBean
public class JDBCxmlBean implements SessionBean {
private final String auditStr = "guarneri.com/JDBCxmlBean - ";
// Channel Module Parameters
private String srcXmlField = "DES_MSG", // Source XML Field to be read (main node)
srcXmlEscaped = "true", // Source XML field is HTML escaped? If false, CDATA section should be used
srcKeyField = "SEQ_MSG", // Source key field that will be inserted in each extracted XML
trgNs = "", // Target message namespace
trgRootElement = "resultset"; // Target document root element (e.g. message type)
AuditMessageKey amk = null; // Needed in order to write out on the message audit log
private int processedRows = 0;
private ModuleContext mc;
public ModuleData process(ModuleContext moduleContext, ModuleData inputModuleData) throws ModuleException {
Object obj = null; // Handler to get Principle data
Message msg = null; // Handler to get Message object
Hashtable mp = null; // Module parameters
ModuleException mEx = null;
Date dstart = new Date();
// Creation of basic instances
try {
obj = inputModuleData.getPrincipalData();
msg = (Message) obj;
amk = new AuditMessageKey(msg.getMessageId(), AuditDirection.OUTBOUND);
mp = (Hashtable) inputModuleData.getSupplementalData("module.parameters");
mc = moduleContext;
} catch (Exception e) {
Audit.addAuditLogEntry(amk, AuditLogStatus.ERROR, auditStr + "Error while creating basic instances (obj,msg,amk,mp)");
throw mEx = new ModuleException(auditStr + "Error while creating basic instances (obj,msg,amk,mp)");
}
Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS, auditStr + "Process started");
// Read of module parameters
// if (mp!=null) {
if (mpget("source.XmlField") != null)
srcXmlField = mpget("source.XmlField");
if (mpget("source.XmlEscaped") != null)
srcXmlEscaped = mpget("source.XmlEscaped");
if (mpget("source.KeyField") != null)
srcKeyField = mpget("source.KeyField");
if (mpget("target.namespace") != null)
trgNs = mpget("target.namespace");
if (mpget("target.RootElement") != null)
trgRootElement = mpget("target.RootElement");
// } else {
// Audit.addAuditLogEntry(amk,AuditLogStatus.WARNING, auditStr + "Module parameters could not be found");
// }
// Extraction of message payload
byte[] payload = msg.getDocument().getContent();
// Payload manipulation
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
extractCLOB(new ByteArrayInputStream(payload), baos);
} catch (Exception e) {
Audit.addAuditLogEntry(amk, AuditLogStatus.ERROR, auditStr + "Error while extracting CLOB content - " + e.getMessage());
throw mEx = new ModuleException(auditStr + "Error while extracting CLOB content - " + e.getMessage());
}
// New payload insertion
try {
XMLPayload newPayload = msg.getDocument();
newPayload.setContent(baos.toByteArray());
newPayload.setContentType("text/xml");
newPayload.setVersion("1.0");
msg.setDocument(newPayload);
inputModuleData.setPrincipalData(msg);
} catch (Exception e) {
Audit.addAuditLogEntry(amk, AuditLogStatus.ERROR, auditStr + "Error while inserting new payload");
throw mEx = new ModuleException(auditStr + "Error while inserting new payload");
}
// Return of manipulated message
Date dend = new Date();
Audit.addAuditLogEntry(
amk,
AuditLogStatus.SUCCESS,
auditStr + "Process completed - " + processedRows + " table rows " + "(execution " + (dend.getTime() - dstart.getTime()) + " ms)");
return inputModuleData;
}
/**
-----------------------------------------------------
Extraction of CLOB field content read by JDBC adapter
-----------------------------------------------------
*/
private void extractCLOB(InputStream in, OutputStream out) throws Exception {
Document doc = null;
Document clob = null;
Document docOut = null;
Element root = null;
Exception ex = null;
// Creation of input Document via DOM
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = null;
factory.setNamespaceAware(true);
factory.setValidating(false);
try {
builder = factory.newDocumentBuilder();
} catch (Exception e) {
throw ex = new Exception("Eccezione durante creazione DocumentBuilder - " + e.getMessage());
}
try {
doc = builder.parse(in);
} catch (Exception e) {
throw ex = new Exception("Eccezione durante parsing documento XML in ingresso - " + e.getMessage());
}
// Preparing output document via DOM
docOut = builder.newDocument();
root = (Element) docOut.createElement(trgRootElement);
docOut.appendChild(root);
// Main part: loop on resultset (table rows issued by JDBC Adpater) and completion of output document
NodeList resultset = doc.getElementsByTagName("row");
Element clobNode = null, keyNode = null;
processedRows = resultset.getLength();
for (int i = 0; i < resultset.getLength(); i++) {
NodeList rowfields = resultset.item(i).getChildNodes();
for (int j = 0; j < rowfields.getLength(); j++)
// Search of XML-containing element
if (rowfields.item(j).getNodeName().equalsIgnoreCase(srcXmlField))
clobNode = (Element) rowfields.item(j);
// Search of keyfield element
else if (rowfields.item(j).getNodeName().equalsIgnoreCase(srcKeyField))
keyNode = (Element) rowfields.item(j);
if (clobNode != null) {
try {
// XML document as string
String clobContent = clobNode.getChildNodes().item(0).getNodeValue();
// Is XML string to be unescaped?
if (srcXmlEscaped.equalsIgnoreCase("true") || srcXmlEscaped.equalsIgnoreCase("yes"))
clobContent = StringEscapeUtils.unescapeXml(clobContent.toString());
// String conversion to inputStream for creation of a new document via DOM
clob = builder.parse(new ByteArrayInputStream(clobContent.getBytes()));
// Add of keyfield
if (keyNode != null && !trgRootElement.equalsIgnoreCase("resultset"))
clob.getFirstChild().appendChild(clob.importNode(keyNode, true));
} catch (Exception e) {
throw ex = new Exception("Eccezione durante parsing documento XML singolo clob - " + e.getMessage());
}
root.appendChild(docOut.importNode(clob.getFirstChild(), true));
Node clobNewNode = clobNode.replaceChild(doc.importNode(clob.getFirstChild(), true), clobNode.getFirstChild());
} else {
root.appendChild(docOut.createComment("No srcXmlField was found in the processed message"));
}
}
try {
// The original document is returned, with "exploded" xmlfield
if (trgRootElement.equalsIgnoreCase("resultset"))
// out.write(doc.toString().getBytes());
out.write(deleteLF(doc.toString().getBytes()));
// serializer.serialize(doc);
// A new document is returned, with "exploded" xmlfield and required target parameters
else
// out.write(docOut.toString().getBytes());
out.write(deleteLF(docOut.toString().getBytes()));
// serializer.serialize(doc);
} catch (Exception e) {
throw ex = new Exception("Eccezione durante scrittura su output stream - " + e.getMessage());
}
}
private byte[] deleteLF(byte src[]) {
byte buf[] = new byte[2 * src.length];
int actualCount = 0;
for (int i = 0; i < src.length; i++) {
if (src[i] == 10) {
buf[actualCount] = 32;
actualCount += 1;
} else {
buf[actualCount++] = src[i];
}
}
byte dst[] = new byte[actualCount];
System.arraycopy(buf, 0, dst, 0, actualCount);
return dst;
}
private String mpget(String pname) {
return (String) mc.getContextData(pname);
}
public void ejbRemove() {
}
public void ejbActivate() {
}
public void ejbPassivate() {
}
public void setSessionContext(SessionContext context) {
myContext = context;
}
private SessionContext myContext;
/**
Create Method.
*/
public void ejbCreate() throws CreateException {
}
}
The module parameters are explained in the table below.
Just one thing more: if the source field it's not escaped, so it's assumed to be a CDATA node!
Bluesky