Working With Flat Files, JSON and other data sources

Previous tutorials explained how to use XQuery to deal with EDI messages, for example, how to transform a directory of EDI messages, create EDI messages out of your database, converting tab delimited files into EDI, etc. But sometimes you're not dealing with EDI; companies still frequently rely on proprietary flat file formats to exchange or circulate information.

A Flat File to XML Example

Consider the following example of a user who needed to convert a file in a proprietary flat file format into a specific XML structure. The sample file looks like this:

This isn't EDI, CSV, tab delimited or any of the other standard or semi-standard ways to represent information in text files; it's a proprietary format based on a specific interpretation of the size and position of the fields. The following is the description of records and fields in the proprietary flat file format:

How can you deal with such a flat file, without building our own parser? DataDirect XML Converters and Stylus Studio can definitely help! XML Converters support "custom conversions"; a custom conversion defines how files belonging to the same format family should be converted to XML by XML Converters. As the definition of a custom conversion can be a tedious task, Stylus Studio provides an editor that allows you to create custom conversion through an intuitive graphical interface. The easiest way to explain how that would work is to watch short online video tutorial which describes

Now that we've converted the proprietary flat file into a specific XML structure, the question becomes, what next?

Processing Converted Flat Files from XQuery

Once a flat file to XML custom conversion has been configured, the combination of DataDirect XQuery and XML Converters proves particularly helpful: not only we are able to move from a proprietary flat file format to well formed XML, but we also have all the power, performance and scalability of XQuery to deal with such XML to transform it (potentially, even augment it!) into the final format that we need. The following XQuery — which implicitly relies on the custom converter described above — does the trick; notice that we created a couple of functions to take care of the positional grouping which is required to generate the desired output; that makes the XQuery much easier to read and more reusable:

Once again, thanks to the fact that DataDirect XQuery and XML Converters make it possible to manipulate a wide variety of data sources (even proprietary flat files!), you are able to deal with transformation (and possibly aggregation) problems from an XML point of view, shielding as much as possible those tasks from the low level details of the data sources.

How to Convert Proprietary Flat Files to XML

In conclusion, DataDirect XML Converters and/or DataDirect XQuery greatly simplifies accessing and working with any non-XML data sources directly from your Java or .NET applications.

A key benefit of ODF and OOXML for developers is the reuse of existing standards

Marc Van Cappellen is a Director of Development at DataDirect Technologies. Marc has more than 15 years of experience in various data access technologies including ODBC, JDBC, ADO, XQJ, SQL and XQuery.

Over the past year we have seen office documents -- spreadsheets, word processing documents, and the like -- taking a more prominent role in business processes, workflows, and vertical applications. While office documents have always been common place, their proprietary binary formats have made them all but unusable to businesses which have sought to integrate office documents in their processes. New office document standards like the OpenDocument Format(ODF) and Office Open XML (OOXML), however, are making office document integration in business processes a reality.

A key benefit of ODF and OOXML for developers is the reuse of existing standards -- in essence, your office documents are XML documents, which makes available a complete palette of tools for manipulating these documents and the information they contain. Using tools and technologies available today, you can transform office documents to HTML or PDF, store them in an XML database, shred their information and store it in a relation databases, embed SOAP messages, enrich them with external information, and so on.

Read the full article: XQuery Your Office Documents

There has been a point of controversy over whether JSON or XML is better at dealing with communication between endpoints. For documents, XML is certainly the leader, supporting namespaces and mixed content. But for communications between live programs, JSON with its implicit hash and array support.

Where the message content can be handled equally well by either, JSON is more succinct, but not as much as one might think. But because the syntax is simpler, it parses more quickly.

But it doesn't matter, because with DataDirect XML Converters, you can transform JSON or XML with equal facility.

A Simple JSON XQuery

Here is a retrieval http call of calendar data from Google's developer calendar.

(In this and several of the following examples, spaces and line breaks are added for clarity.)

The portions in this color are the actual XQuery syntax, which includes escaping the "&" characters. The portion in this color is the call to the converter.

What the above mini XQuery program (yes, it is actually a complete program) does is fetch the calendar data as JSON and convert it to XML. Because we haven't specified any transformations, the content will just be output.

Taking the resulting JSON from the raw http: query would result in something like this (formatted for ease of reading):

which is clearly some sort of nested tree structure. Since XML is also a nested tree structure,* and XQuery actually operates on the XQuery Data Model and not on just raw XML bits, using the JSON XML Converter we can transform this on-the-fly to XML input for another application, including the DataDirect XQuery Engine.

(* actually, this looks like XML converted to JSON, which we're going to deconvert.)

This means the XQuery engine effectively "sees" something like the following — but remember, this is a virtual translation, which means there is no separate step that saves something as JSON and then reloads it as XML. It all happens inside of the JSON XML Converter library.

Once the data "looks" like XML, XQuery can transform it. So to see all of the names and dates of the next four upcoming entries, the following XQuery program will do just that:

Which of course produces the following schedule of upcoming Google-related events:

JSON versus XML — A Moot Point

The real advantage of the DataDirect XML Converters is that they make file format issues irrelevant. Just as DataDirect XQuery is a highly conformant W3C XQuery processor that works the same against any of the databases it is connected to — whether Oracle, IBM DB2, Sybase, MySQL, SQL Server or whatever — so do the XML Converters mask the difference between different storage formats.

So the JSON adherents can have their way, and the XML adherents theirs, but you can bridge the gap and process data from either or both sources without difficulty.

We hope you enjoyed this overview of DataDirect XML Converters™'s runtime library. Happy Java programming!

Ivan Pedruzzi, The DataDirect XML Converters™ Team