Sorry, I had meant to reply sooner. But maybe it's for the best -- it helps to set your expectations, which I think are basically about right. InDesign's XML workflows are not very mature and it's not going to be able to do what you want without some effort. And that means you're going to have to do some work with InDesign's scripting. Are you familiar with it?
I have thought of using XSLT to transform the XML file to a more readable format for the indesign template as the orderof elements needs to be changed and some elements need to be duplicated. Some of the image and text files can be converted into an indesign file so that when required, the corresponding file can be appended to the booklet.
InDesign has two seperate and completely independant XML models. The first one, that is heavily focused on in the documentation, is document-based. You import XML into a document and then do stuff with it, such as creating text frames or stories from some nodes, rectangles from other nodes, etc., possibly according to a template though possibly not.
Which of these makes more sense is a bit unclear here. It may very well depend on how good a match your XML is for InDesign's native model. Though of course you could use both.
In particular, the native model doesn't make a lot of sense for multiple documents. Though I suppose you can import your XML into one master document, then parse the tree and create a booklet document for each child of the root, and then parcel out the XML into those documents.
Of course, depending on your booklets, perhaps you want to have them consecutive in one big InDesign document.
I'd probably go with the E4X method. Note that you may be able to use that instead of XSLT, and if you find that programming in XSLT is not what comes easily to you.
From there, I am stumped. I would like to ideally create an automatic process where once the XML file is imported and the XSLT applied; Indesign automatically creates the booklets and populates the correct text and images from a database and indesign files.
All perfectly possible. You'll have to give a lot more detail for us to be able to help you reasonably, though. Posting an example XML file would be a great way to start. Maybe some screenshots of the layout. How variable does the layout have to be? How many textframes are htere? Are they all one linked story? How do the graphics work?