Transformation and lookups
When talking about integrations between application and/or services we can't escape the fact that messages need to be transformed. Most of the time the applications and/or services do not talk the same language (that is, message structure or even data types, for example, milliseconds from epoch versus date time). Besides transforming the structure, we sometimes need to convert values (that is, domain value mapping).
Transformations
Oracle Integration Cloud Service uses XML as its message format for its data objects and messages. To transform XML-based messages from one structure to another there are two main open standards that can be used to manipulate data; XQuery (more info at https://www.w3.org/TR/xquery/) and XSLT (https://www.w3.org/TR/xslt).
XQuery
XQuery is like the name suggests, a query language, but besides that it is also a functional programming language. It queries and transforms collections of data, both structured as unstructured. It can transform messages to XML, but also text and other data formats, for example, JSON and HTML.
Using XQuery we can extract and manipulate XML documents or any source that can be viewed in XML, such as office documents or database schemas. It has built-in support for XPath expressions. With XPath we can address specific nodes within the XML document. XQuery is a SQL-like query language that supplements XPath and uses FLWOR expressions for performing joins. A FLWOR expression is named after the five parts it is constructed of: FOR, LET, WHERE, ORDER BY, and RETURN.
XQuery has the capability to transform, and it also allows us to create new XML documents. Where normally the elements and attributes are known in advance, it can use expressions to construct dynamic nodes, including conditional expressions, list expressions, quantified expressions, and so on. A simple example of XQuery is shown as follows:
<html><body>
LET $book := doc("bookstore.xml")/book
FOR $ch in $book/chapter
WHERE $book/chapter/num < 10
ORDER BY $ch/pagecount DESC
RETURN <h2>{ $ch/title }</h2>
</body></html>
XSLT
Originally an acronym for Extensible Stylesheet Language Transformations, XSLT is as the name suggests, a language for transforming XML documents and is basically a style sheet to transform XML documents into other XML documents, or other data formats such as (X)HTML, XSL Formatting Objects (such as generating PDFs), RSS, and non XML (such as CSV):
XSLT processing takes an XML source document and a XSLT style sheet, and produces a new output document. The XSLT style sheet contains static XML that is used to construct the output structure. It uses XPath expressions, just like XQuery, to get data from the XML source. The XSLT language includes logic (if-else) and operators (for-each) to process the XML source.
An XSLT style sheet is an XML document containing the root node <xsl:stylesheet>, which is declared by the xsl prefix and is mandatory. An XSLT style sheet contains one or more <xsl:template> elements and other XML elements defining transformation rules:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<h2>Book Collection</h2>
<table border="1">
<tr>
<th>Title</th><th>Author</th>
</tr>
<xsl:for-each select="bookstore/book">
<tr>
<td><xsl:value-of select="title"/></td>
<td><xsl:value-of select="author"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
Integration Cloud Service currently only supports XSLT, because it is a transformation/mapping language, which can easily be made visually. Let's discuss XSLT in more detail.
XPath expressions
XSLT, like XQuery, uses XPath expressions to address specific nodes within the source document and perform a calculation. XSLT can use a range of functions that XPath provides to further augment itself.
Originally an acronym for XML Path Language, XPath is used for selecting nodes from an XML document. It models XML as a tree of nodes. XPath is named after its use of a path notation for navigating through the hierarchical structure of an XML document. XPath uses a compact, non-XML syntax to form expressions for use in Uniform Resource Identifier (URI) and XML attribute values.
XPath models an XML document as a tree of nodes. The following figure depicts the different types of nodes, as seen by XPath, in an XML document:
The elements in the preceding diagram are:
- A document root, which is a virtual document root that is the parent of the entire XML document containing the XML declaration, the root element and its children, comments, and processing instructions at the beginning and the end of the document.
- A root element (for example, the
<bookstores>element). - Element nodes (for example, the
<bookstore>,<store_name>, and<store_url>elements). The root element is also an element node. - Attribute nodes (for example, the
num="1"node). - Text nodes (for example, the nodes containing the text, Packt Publishing).
In the XPath model, the document root is not the same as the root element node. XPath defines a way to compute a string value for each type of node, some of which have names. Because XPath fully supports the XML Namespaces Recommendation, the node name is modeled as a pair known as the expanded name, which consists of a local part and a namespace URL, which may be null.
XPath allows various kinds of expressions to be nested with full generality (that is, using wildcards). It is a strongly typed language, such that the operands of various expressions, operators, and functions must conform to designated types. XPath is a case-sensitive expression language.
XPath Examples
The following are some examples of XPath expressions:
Get the store_name node of the bookstore identified with number 1:
/bookstores/bookstore[@num="1"]/store_name
Get the text value of the store_name node of the last bookstore:
/bookstores/bookstore[last()]/store_name/text()
Check if a bookstore exists in the source document (prefixing // means a wildcard search):
count(//bookstore[store_name="Packt Publishing"]) = 1
XLST constructs
With XSLT it is possible to create really dynamic output documents using XSLT constructs together with XPath expressions. XLST construct are specific elements that are defined by the XSLT standard. XSLT defines the following construct elements: output, template, value-of, for-each, if, and choose.
The xsl:output element
The output element specifies the output format of the result tree and it must be a direct child of the style sheet root element. The element has a few attributes. The method attribute defines which processor needs to be used, such as xml, html, or text, and the media-type attribute defines the target type, for example, application/json:
<xsl:output method="html" media-type="text/xml"/>
The xsl:template element
The template element defines a template rule that matches a specific node in the source document using an XPath pattern value. The output expression contains the formatting instructions to produce a result tree:
<xsl:template match="pattern"> output-expressions </xsl:template>
The match pattern determines the context node within the template. The most common match is on the root element of the source document tree:
<xsl:template match="/"> A simple text string </xsl:template>
A template can also be given a name and called by its name within another template by passing a parameter:
<xsl:template name="minValue">
<xsl:param name="values"/>
...
</xsl:template>
<xsl:template match="/">
<xsl:call-template name="minValue">
<xsl:with-param name="values"
select="bookstore/book/year"/>
</xsl:call-template>
</xsl:template>
The xsl:value-of element
The value-of element is used to insert the text value of the expression. This element defines a select attribute, which contains an expression and is used inside a template element:
<xsl:template match="name"> Name: <xsl:value-of select="name"/> </xsl:template>
The xsl:for-each element
The for-each element is used to loop over node-sets. This element defines a select attribute, which instructs the XSLT processor to loop over the node set returned by the given XPath expression:
<xsl:template match="/">
<xsl:for-each select="catalog/book">
<p><xsl:value-of select="title"/></p>
</xsl:for-each>
</xsl:template>
Inside the for-each element, the current node in the set is the context. The position() function returns the index in the loop, that is, the iteration counter. It is also possible to sort the order in which the nodes are looped over using the xsl:sort element. It defines a select attribute that contains an expression whose value is sorted on:
<xsl:for-each select="catalog/book">
<xsl:sort select="year" data-type="number" order="descending"/>
<p>
<xsl:value-of select="title"/>
</p>
</xsl:for-each>
To apply multiple criteria, we can use several xsl:sort elements after each other.
The xsl:if element
Sometimes a section of the XSLT tree should only be processed under certain conditions. With the if element we can build that conditionally. The if element defines a test attribute, which contains an XPath expression that should evaluate to a Boolean value:
<xsl:for-each select="catalog/book">
<xsl:if test="year = 2016" >
<p><xsl:value-of select="title"/></p>
</xsl:if>
</xsl:for-each>
There is no else statement, but for this XSLT defines the xsl:choose element.
The xsl:choose element
XSLT supports multiple mutually exclusive branches with the choose element. The xsl:choose element contains xsl:when tags that define parallel branches, one of which is executed. The xsl:otherwise tag can be used to define a default branch.
We use the choose element for if-the-else constructions:
<xsl:for-each select="catalog/book">
<xsl:choose>
<xsl:when test="year = 2016" >
<xsl:text>New Arrivals</xsl:text>
</xsl:when>
<xsl:when test="year < 2015" >
<xsl:text>Our Classics</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>Top Picks</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
When implementing integrations with ICS we will use XSLT for creating mappings between source and target connections. The major advantage of ICS is that we can build these mappings with a visual editor.
Lookups
A lookup, also known as Domain Value Maps (DVM), associates values used by one application for a specific field to the values used by other applications for the same field.
They enable us to map from one vocabulary used in a given domain to another vocabulary used in a different domain. For example, one domain may represent a country with a long name (Netherlands), while another domain may represent a country with a short name (NL). In such cases, you can directly map the values by using domain value maps.
Simple example:
|
CountryCode |
CountryName |
|
NL |
Netherlands |
|
UK |
United Kingdom |
|
USA |
United States |
A lookup also supports qualifiers. A mapping may not be valid unless qualified with additional information. For example, a DVM containing a code-to-name mapping may have multiple mappings from ABE to Aberdeen because Aberdeen is a city in both UK and the USA. Therefore, this mapping requires a qualifier (UK or USA) to qualify when the mapping becomes valid.
Qualifier example:
|
Country (qualifier) |
CityCode |
CityName |
|
United Kingdom |
ABE |
Aberdeen |
|
United States |
ABE |
Aberdeen |
|
Netherlands |
AMS |
Amsterdam |
Qualifier order support
We can also specify multiple qualifiers for a lookup using a qualifier order. Using this, ICS can find the best match during lookup at runtime. Hierarchical lookups are supported. If you specify a qualifier value during a lookup and no exact match is found, then the lookup mechanism tries to find the next match using the following qualifier. It proceeds until a match is found, or until no match is found even with all qualifiers set to an empty value.
One-to-Many Mapping Support
We can map one value to multiple values in a domain value map. For example, DVM for subscription payments can contain mapping to three values such as discount percentage, discount period, and subscription period.
