Using XMLToaster - a simple example.

This short example to show you how to extract data from a relational database into a n XML structure. The database it uses is shown in the appendix.
If what you need to do is a little more complex, there is a bigger “unioned select” example over on the sourceforge wiki.

First we will generate a list of books and their author…

   xmlselect using noschema
   select b.bookTitle into xml:{Books.Book.title},
         b.isbn into xml:{Books.Book.isbn},
         a.firstName into xml:{Books.Book.Author.firstName},
         a.surname into xml:{Books.Book.Author.surname}
  from books b, authors a
  where b.authorId = a.authorId
  order by bookTitle newValue throwNew xml:{Books.Book}

And here’s an example of the XML that would be generated;

  <books>
    <book>
      <isbn>0-09-645233-2</isbn>
      <author>
        <firstname>Mario</firstname>
        <surname>Brothers</surname>
      </author>
    </book>
    <book>
      <isbn>0-02-737665-1</isbn>
      <author>
        <firstname>Michael</firstname>
        <surname>Crichton</surname>
      </author>
    </book>
    <book>
      <isbn>0-340-71285-6</isbn>
      <author>
        <firstname>Charlotte</firstname>
        <surname>Lamb</surname>
      </author>
    </book>
  </books>

The first line of the xmlselect statement (xmlselect using noschema) indicates that the data will not be restricted by an XSD schema definition. If a schema was specified then only XML elements specified in the schema would be populated by the statement and elements would be created in the order specified in the schema.

The next thing to note is how the selected column is mapped into the XML structure (into xml:{Books.Book.title}). The text between the curly brackets indicates the path in the XML structure to where you want the column data to go. Any parent elements not in the structure will be created by XMLToaster. If the column data is to be mapped to an XML attribute you can specify it as follows; into xml:{Books.Book.title.@bookId}

The final thing to note about the statement is the order by clause (order by bookTitle newValue throwNew xml:{Books.Book}).

This is how we tell XMLToaster when to create a new element in the output XML, i.e. whenever there is a new book title in the selected resultset create a new ‘Book’ element. When a new element is created by the Toaster any data that is mapped to elements/attributes underneath it will be mapped to the new branch in the XML (any branches already created will be left alone), therefore we wont overwrite any of the previous book details.

In the next example, we want to get a list of all the authors, the books that they have written and who published them. To do this we will use the query shown below;

xmlselect using noschema
select a.authorId,
      a.firstName into xml:{Authors.Author.firstName},
      a.surname into xml:{Authors.Author.surname},
      b.bookId,
      b.bookTitle into xml:{Authors.Author.Book.title},
      b.isbn into xml:{Authors.Author.Book.isbn},
      p.publisherName into xml:{Authors.Author.Book.publisher}
from authors a,
    books b,
    publishers p
where a.authorId = b.authorId
and   b.publisherId = p.publisherId
order by authorId newValue throwNew xml:{Authors.Author},
      bookId newValue throwNew xml:{Authors.Author.Book}

Here is an example of the XML that would be generated;

The things to note about the select statement this time are that;

1. Columns can be selected (and used in the order by clause) but don’t have to be mapped into the XML structure.

2. We have multiple ‘throwNew’ clauses

   Bookmark & Share:

   Share or bookmark this page.

   • 
   • 
   • 
   • 
   • 
   • 
   • 
   • 
   • 
   • 
   •