A column that contains XML (a CLOB / VARCHAR with an XML document) comes back from the SQL integration as a String; parse it in the same service flow with E4X (8.5.x) or the Java DOM / XPath (any version, required on BAW 20+):
// service flow: SQL Execute Statement -> tw.local.rows (returnType e.g. "OrderRow" with a field payloadXml String)
// script after it (BAW 20+ / any version):
var dbf = javax.xml.parsers.DocumentBuilderFactory.newInstance(); dbf.setNamespaceAware(false);
var xp = javax.xml.xpath.XPathFactory.newInstance().newXPath();
tw.local.orders = new tw.object.listOf.Order();
for (var i = 0; i < tw.local.rows.listLength; i++) {
var doc = dbf.newDocumentBuilder().parse(new org.xml.sax.InputSource(new java.io.StringReader(tw.local.rows[i].payloadXml)));
var o = new tw.object.Order();
o.number = String(xp.evaluate("/order/@number", doc));
o.customer = String(xp.evaluate("/order/customer/name/text()", doc));
var lines = xp.evaluate("/order/lines/line", doc, javax.xml.xpath.XPathConstants.NODESET);
o.lines = new tw.object.listOf.OrderLine();
for (var j = 0; j < lines.getLength(); j++) {
var l = new tw.object.OrderLine(); l.item = String(xp.evaluate("@item", lines.item(j))); l.qty = parseInt(xp.evaluate("@qty", lines.item(j)), 10);
o.lines.insertIntoList(j, l);
}
tw.local.orders.insertIntoList(i, o);
}
// BPM 8.5.x alternative with E4X: var x = new XML(tw.local.rows[i].payloadXml); o.number = String(x.@number); o.customer = String(x.customer.name);Alternatives: let the database do it - Db2 XMLTABLE / Oracle XMLTABLE / SQL Server .value() return the XML parts as columns, so the BPM side gets plain rows (fastest for large sets); or, when the XML follows a schema you own, map it to a business object once with a toolkit service (question 109 for SOAP-shaped documents). Namespaced XML: set setNamespaceAware(true) and use local-name() in the XPath, or register a namespace context.
References