Design pattern for creating an XML document from a database query that has a 1:many relationship

Question

All,

Sorry in advance for the ambiguous title.

Here's what I'm trying to do...

I'm working on an application that needs to query a database to get a list of items. That list of items will get formatted into an XML document that gets returned to the querying front-end.

To simplify, let's say that the items are books. Each book has the following attributes: ISBN, Title, Author, Price. Each book ALSO has between 0 and n reviews

So, the data is stored in 2 tables: "book_info" and "book_reviews". Each book has one row in "book_info", and between 0 and n rows in "book_reviews".

I would like the XML that gets returned to look like this:

<books>
    <book isbn='xxxxxx' title='abcde' author='xzy' price='99.99'>
        <review>blah, blah, blah</review>
    </book>
    <book isbn='xxxxxx' title='abcde' author='xzy' price='99.99'>
        <review>blah, blah, blah</review>
        <review>blah, blah, blah</review>
        <review>blah, blah, blah</review>
    </book>
    <book isbn='xxxxxx' title='abcde' author='xzy' price='99.99' />
</books>

As you can see, the first book has one review, the second has three, and the third has none.

My question is this: what's the most efficient way to build a structure like this?

For example, I can get the list of books like this:

Select isbn, title, author, price from book_info where title like "%learn to%"

By iterating through the result set, I can build the XML document. But how do I get the reviews?

An obvious solution is this: with each interation, get the isbn for book in that row, then query the database again:

select review from book_review where book_review.isbn = [the current isbn]

By iterating though this "sub"-result set, I can add the child "review" nodes to the current book node.

The problem with this is that, if there's 100 books in the initial result set, I have to run 100 SQL commands to look for all the reviews.

So, a second thought was this:

1. Select all the books matching the search with one query
2. Build the XML document
3. Execute one query against "book_reviews" to retrieve all the reviews for all the books in step 1 (e.g., "select review from book_reviews where isbn in ([list of isbn])")
4. Iterate through that result set; for each review, find the XML node for the book it corresponds too, and append a child node to it

This would mean just two SQL queries, but searching through the XML document.

Ultimately, these both seem a little inelegant. Since this seems like it must be a common problem, I'm hoping there's a nice, elegant solution for it.

For what it's worth, I'm using SQL Server and PHP to execute the queries and assemble the XML document.

Many thanks in advance for any guidance and insight.

Cheers, Matt

Answer 1

If you're using Microsoft SQL Server, you can have it generate the XML for you with the FOR XML clause. The usage can be a little tricky if you are trying to get precise formatting, but it's fairly versatile.

If you really, truly need to generate it yourself, then just use a single select with a join to the book reviews. I would be a little surprised if someone hadn't already written some kind of an XML generator from result set functionality in PHP.

An example query:

SELECT
    BI.isbn,
    BI.title,
    BI.author,
    BI.price,
    BR.review
FROM
    Book_Info BI    -- It's usually considered bad form to name a table suffixed with "Info" or "Data". Just "Books" would be better
LEFT OUTER JOIN Book_Reviews BR ON BR.isbn = BI.isbn
WHERE
    BI.title LIKE '%learn to%'
ORDER BY
    BI.isbn