The last post touched in the transformation capabilities in XIP’s SQL engine. Now, we turn to its dual-core counterpart, the XQuery engine.
XIP can federate queries over relational, XML and other semi/unstructured data sources. On the consumption side, it can expose a virtual relational or XML database interface to applications. Inherent to this is the ability to transform data from various source formats to required target formats. The XML-2-XML transformation functionality in XIP is available through its XSLT and XQuery engines. These engines provide a standards based, powerful way of specifying any arbitrary transformation from one XML source format to another. In this post we will focus on the XQuery engine, but a number of ideas also apply to the XSLT engine in that both these engines share the same, extensible function library.
Typical transformation abilities in the data integration scenario include schema conversions including and not limited to changes in document structure, combining/splitting or otherwise transcoding content of elements and attributes, joining, filtering, union, aggregating/summarizing etc. XQuery provides constructs for all of these. I will show a few examples and give some direction as to how the platform can be extended to achieve more complex transformations. As an EII platform, XIP does not have nearly as comprehensive a transformation tool set as you would expect from a leading ETL vendor. But this is also appropriate, since real time data integration is typically not suited for very complex or computationally expensive transformations.
Functions and Operators:
To start with the basics, XIP’s XQuery engine comes with an extensive function library which includes all the basic functions for string manipulation, numerical functions such as floor/ceiling/round, aggregate functions such as avg/min/max/count etc., data type conversions, date manipulation functions and so on. Also available are a number of operators for number and date arithmetic. This is a rich set of functionality for creating new values from source data.
Structural Transformations:
Utilizing the path expressions, Conditional expressions, Element constructors and constructs like FLWOR, there are virtually limitless ways you can process a source document structure and construct an arbitrarily complex output XML structure. I will illustrate this point through a transformation analogous to a pivot in the relational world. Consider the sample document shown below:
Converting this structure to use one in which the data values become element names is a simple matter of using computed element constructors along with a FLWOR construct.
You can see that an approach like this can tackle arbitrarily complex structural transformations.
Unions are another common operation in an ETL scenario. A simple a query that combines a series of customer records from an XML view over on Oracle database and a view over the salesforce.com web service would be written as follows:
<LargeCustomers>
{view(“oradata/crm”)/Customers/Customer[NumEmp > 5000]
union
view("salesforce.com/Account")/AccountList/Account[NumberOfEmployees > 5000]
}
</LargeCustomers>
The expressions that generate the customer elements (in this case, the path expressions) can be arbitrarily complex XQuery expressions.
Extensibility
XIP’s XQuery engine also allows custom transformations by allowing you to code custom external functions in Java. This allows for just about any transformation that you can dream up. The XQuery modules extension feature also allows you to package up common enterprise transformation functions as a library that can be shared by other application developers and data architects.
What you have seen in this post is but a glimpse of XML transformation capabilities in the XIP platform. Get a download and start playing with these features to see what else you can do.
