What is XMLToaster - And why should I be interested?
The Smell of Burning XML - Introducing XMLToaster
Java can be a very blunt tool when it comes to working with XML - there are lots of concepts to understand, lots of APIs to learn, and lots of code to write. Even when you have made it through the concepts and APIs you’ve only made it to the starting line!
Now you need to get some data to go with your XML, which often means learning yet another API to integrate with your relational database!
In this article I will explain how the XMLToaster can simplify the process of working in the XML and relational database worlds, without giving away any of the power and flexibility of either.
The examples in this article are based on the database shown in figure 1 which supports the XML messages we will be generating for an imaginary address book system.
Figure 1. The database schema
A simple example
For the first example of an XMLToaster query I will show you how to generate a list of names from the data in the “person” table.
xmlselect using noschema
select
p.firstname into xml:{addressBook.person.firstname},
p.surname into xml:{addressBook.person.surname}
from
person p
order by
surname newValue throwNew xml:{addressBook.person}
If you are familiar with SQL you will see that it’s a normal SQL select statement with a little extra “syntactic sugar” sprinkled on top. In fact, I am going to assume that you’re familiar with the SQL “select … from.. order by” syntax so I will just describe the extra statements that tell the XMLToaster what the XML output is going to look like.
Starting from the top, the statement “xmlselect using noschema” indicates that the generated XML doesn’t have to conform to an XML schema. For simple queries this is normally fine and for complex messages where fine control over the generated XML is essential you can specify a schema (as we shall see in a later example).
The “into xml:{addressBook.person.firstname}” says where each person’s firstname will appear in the output XML. In this case it will go underneath <addressBook><person> and appear in an element called <firstname>.
Finally, “newValue throwNew xml:{addressBook.person}” after surname in the “order by” part of the select statement means that a new <person> element will be created in the output XML every time a new value of surname is encountered, so we’ll get a new <person> block with every new surname.
Here is the XML that is generated by the query…
<?xml version="1.0" encoding="UTF-8"?>
<addressBook>
<person>
<surname>Abbingdon</surname>
<firstname>Alice</firstname>
</person>
<person>
<surname>Bloomsbury</surname>
<firstname>Bob</firstname>
</person>
<person>
<surname>Chesterfield</surname>
<firstname>Charles</firstname>
</person>
</addressBook>
Example using some of SQL features and a child XML element
Since the XMLToaster syntax is similar to standard SQL you can write something a little more complex and can join tables, use SQL functions and restrict data with a normal where clause.
The query below expands the first example. Now the
xmlselect using noschema
select
p.personId,
concat(p.firstname, ' ', p.surname) into xml:{addressBook.person.name}
from
person p
xmlunion
select
p.personId,
n.nicknameId,
concat('A.K.A. ', n.nickname) into xml:{addressBook.person.nickname}
from
nickname n,
person p
where
p.personId = n.personId
order by
personId newValue throwNew xml:{addressBook.person},
nicknameId newValue throwNew xml:{addressBook.person.nickname}
In this query we are using the “concat” function to format the SQL data before we put it into the XML structure.
The query also contains the keyword “xmlunion” which is similar to the standard SQL “union” keyword, in that it pools the
results of the various select statements, but you don’t have to have the same resultset columns in each select clause.
The order by clause of this query contains two columns, and two “throwNew” keywords. This allows us to create a new “person”
element whenever the “personId” changes in the resultset, and to create a new “nickname” element underneath the correct
“person” element for each nickname that is returned.
When the query is run against our test database, XMLToaster produces the following XML:
<?xml version="1.0" encoding="UTF-8"?>
<addressBook>
<person>
<name>Alice Abbingdon</name>
<nickname>A.K.A. Ally</nickname>
</person>
<person>
<name>Bob Bloomsbury</name>
<nickname>A.K.A. Bobby</nickname>
<nickname>A.K.A. Billy</nickname>
</person>
<person>
<name>Charles Chesterfield</name>
</person>
</addressBook>
An awkward example - When the tables and XML schema don’t match.
In this next example we are going to generate an XML message that lists the people and their home and work addresses.
In the database the home and work addresses are kept in the same table (personAddress), but the XSD schema that we have been asked to use has the two types of addresses separated in the XML heirachy. This can be awkward for traditional forms of relational to XML mapping because they like the XML data heirachy to closely match the database table relationship heirachy. Unfortunately in real life there are frequently differences - maybe the database was designed to different requirements than your application (and vice versa), or maybe it’s a legacy database. This can force you to perform unnecessary transformations of the XML you get from the database into the XML that you really require, which is costly in terms of development/maintenance effort and system performance.
OK, back to the code. This is how you can generate the list of people and their addresses.
xmlselect using schema 'addressList.xsd'
/* Person Name - put person's name into <person> */
select
p.personId,
p.firstName into xml:{addressBook2.person.firstname},
p.surname into xml:{addressBook2.person.surname}
from
person p
xmlunion
/* Home Address - if they have a home address put that into <person><home> */
select
p.personId,
a.line1 into xml:{addressBook2.person.home.line1},
a.line2 into xml:{addressBook2.person.home.line2},
a.city into xml:{addressBook2.person.home.city},
a.state into xml:{addressBook2.person.home.state},
a.zip into xml:{addressBook2.person.home.zip}
from
address a,
personAddress p
where
p.addressId = a.addressId
and p.addressType = 'HOME'
xmlunion
/* Work Address - if the person has a work address, put that into <person><work> */
select
p.personId,
a.line1 into xml:{addressBook2.person.work.line1},
a.line2 into xml:{addressBook2.person.work.line2},
a.city into xml:{addressBook2.person.work.city},
a.state into xml:{addressBook2.person.work.state},
a.zip into xml:{addressBook2.person.work.zip}
from
address a,
personAddress p
where
p.addressId = a.addressId
and p.addressType = 'WORK'
/* notice that XMLToaster uses the "order by personId" to join all three unions together */
order by
personId newValue throwNew xml:{addressBook2.person}
In the query statement we have three different selects separated by the “xmlunion” keyword.
The three seperate select’s are then effectively joined together in the order by clause of the statement,
such that data with the same “personId” is processed by XMLToaster together.
In this way you can build in as much complexity to your selects as you require.
When the query is run against our test database, the following XML is produced:
<?xml version="1.0" encoding="UTF-8"?>
<addressBook2>
<person>
<firstname>Alice</firstname>
<surname>Abbingdon</surname>
<home>
<line1>21 Pardee Lane</line1>
<line2 nil="true" />
<city>Oxford</city>
<state>California</state>
<zip>765231</zip>
</home>
<work>
<line1>37 Edgewater Drive</line1>
<line2 />
<city>Cambridge</city>
<state>Calfornia</state>
<zip>655332</zip>
</work>
</person>
<person>
<firstname>Bob</firstname>
<surname>Bloomsbury</surname>
<home>
<line1>7 Capwell Drive</line1>
<line2 />
<city>Melrose</city>
<state>California</state>
<zip>765783</zip>
</home>
<work>
<line1>18 Pendleton Way</line1>
<line2 />
<city>Eastcote</city>
<state>California</state>
<zip>765656</zip>
</work>
</person>
<person>
<firstname>Charles</firstname>
<surname>Chesterfield</surname>
<home>
<line1>15 Wistar Road</line1>
<line2 />
<city>Westbridge</city>
<state>California</state>
<zip>765322</zip>
</home>
<work>
<line1>42 Ashton Avenue</line1>
<line2 />
<city>Northville</city>
<state>California</state>
<zip>76529</zip>
</work>
</person>
</addressBook2>
Hidden power
In this last example I am going to show how you can use XMLToaster to produce an XML message containing the results of a search against the database.
The requirement is to find a distinct list of people with either a firstname or nickname that matches the search name. A query to select this from our database would be
select distinct p.personid, firstname, n.nickname, surname
from person p left outer join nickname n on n.personid = p.personid
where p.firstname like 'Bob%' or n.nickname like 'Bob%'
This may sound straightforward, but what happens when the search criteria match both a persons’ firstname and their nickname? For instance if we search for “Bob*” and it matches someone with a firstname of “Bob” and nickname of “Bobby” they would appear in the results twice
This would result in the person appearing multiple times in the select resultset, something that we don’t want.
We could try using select distinct to cure the problem, because, assuming you need to know which nickname the match was against, the resultset rows will already be distinct. To solve this problem we can use the power of the XMLToaster “throwNew” keyword,
which gives us control over when a new element is created in the XML - in these circumstances, only when a new person is returned in the resultset.
Here is the select statement to perform the search (note that parameters are passed into XMLToaster queries using the “${XYZ}” syntax).
xmlselect using noschema
select
p.personId,
p.firstName into xml:{searchResults.person.firstname},
p.surname into xml:{searchResults.person.surname}
from
person p
where
firstName like '${NAME}%'
xmlunion
select
n.personId,
n.nicknameId,
p.firstName into xml:{searchResults.person.firstname},
p.surname into xml:{searchResults.person.surname},
n.nickname into xml:{searchResults.person.nickname}
from
person p,
nickname n
where
p.personId = n.personId
and n.nickname like '${NAME}%'
order by
surname,
personId newValue throwNew xml:{searchResults.person},
nicknameId newValue throwNew xml:{searchResults.person.nickname}
When run with the “NAME” paramater set to “B” we get a message with a single instance of Bob, even though the search criteria matched his firstname and two of his nicknames.
<?xml version="1.0" encoding="UTF-8"?>
<searchResults>
<person>
<surname>Bloomsbury</surname>
<firstname>Bob</firstname>
<nickname>Bobby</nickname>
<nickname>Billy</nickname>
</person>
</searchResults>
Saving XML into a database
XMLToaster also provides a means of saving XML messages into a relational database without first transforming the XML, converting it into an object and then performing an Object-to-Relational mapping. This is achieved by specifying where in the input XML the data for each column of a table or parameter of a stored procedure comes from. These mapping specifications can be more complicated than the simple SQL select syntax used to get XML data out of a database, but are still very intuitive and powerful. You can see an example here.
System Performance (There’s a RAT in the system what am I gonna do?)
Traditional heirachical processing tends to have a naive brute-force approach to extracting data where each level of the heirachy is fetched from the database a child-at-a-time. This means that for each row in our “person” table, using traditional relational to XML mapping techniques would perform two singleton selects to get their home and business addresses. If we have 1000 rows in the person table, this means 2000 singleton selects executing against the database each time we want to produce the address list.
This doesn’t sound too bad in theory, after all the same amount of data is ultimately read from the disk, right? In practice it is nearly always orders of magnitude slower. Relational databases perform much, much better when extracting (say) 5000 rows in a single query than running 5000 queries that extract a row each.
Another significant drain on performance that heirachical processing fetches from the database a child-at-a-time, executing many small select statements on the database server rather than a few big statements.
For instance, in our “person” table, using traditional relational to XML mapping techniques, we would have to perform two singleton selects to get their home and business addresses. If we have 1000 rows in the person table, this means 2000 singleton selects executing against the database each time we want to produce the address list. The problem gets proportionally worse the more levels there are to the hierachy and can cripple an application.
Many of the performance problems in database-centric applications can be traced to this kind of iterative processing of database rows (often referred to as “Row At a Time” processing, or RAT’s). These tight loops of small statements continually thrown at the DBMS are one of the best ways to send your DBA insane (or at least a bit more insane), as they are difficult to mediate against unless you control the source code. Throwing hardware and memory (i.e $money) at this problem tends to result in linear improvements. Good indexing and caching can move some of the load from disk to cpu, but the ultimate fix is to change the coding to be “set oriented”. This requires far more complex back-end processing than the naive brute force approach to build the correct select statements, but can give exponential performance improvement.
This is exactly what XMLToaster is designed to do. By giving the developer the power to extract objects using unrestricted joins, we make use of the set processing performance boost provided by the relational database.
Conclusion
In this short introduction to XMLToaster I hope that I’ve been able to show that it is an easy to use, yet powerful tool for building XML messages from relational database data. Its’ simplicity lends itself to situations where you don’t need the overhead of a heavywieght toolset, for example where XML is just the data format and you don’t need to hold the data in a transient object. I also think that by being able to work directly to an XSD schema the development process for XML messaging systems is vastly improved. This allows you to skip any transformation steps that might have been required to convert the data extracted from the database structure into the structure that the message consumer requires. This should speed up development times and, just as importantly, decrease maintainence effort when things inevitably change.
An article like this we can only scratch the surface of what you can do with XMLToaster. For anyone who wants to learn more about using XMLToaster I would advise that they read the tutorial which is referenced in the resources section below. If you don’t find what you’re looking for there then feel free to contact us, or, for the ultimate documentation there is always the source code available at sourceforge ;)
Resources
- Sample code for this article
- Toaster Documentation Page
- SourceForge home of XMLToaster code
