Creating an Access Database structure from an xml document

[bullchina]

I have an xml structure that I want to use in a web page with php that looks like this:

[code]<projects>
        <project>
                <name> ! Test Project </name>
                <projectdate></projectdate>
                <constructiondate> 2006 </constructiondate>
                <location> Barcelona, Spain </location>
                <description>
                        <![CDATA[
<p>This is a test project description with two paragraphs</p><p>this is the second paragraph</p>
          ]]>
                </description>
                <type>buildnigGarden</type>
                <team></team>
                <budget></budget>
                <area></area>
                <client></client>
                <info> this is a field for extra information, about the clients, the size, price,
                        etc... </info>
                <directory> test </directory>
                <images>
                        <image name="test.porch.jpg" title="this is image 1."/>
                        <image name="test.firstfloor.jpg" title="this is image 2."/>
                        <image name="test.firstfloor2.jpg" title="this is image 2."/>
                </images>
                <files>
                        <file/>
                        <file/>
                </files>
                <status> built </status>
        </project>
</projects>[/code]

and i have to store this same information structure in a microsoft access database. i have no idea how to get a list of images or a list of files into an access database, and i'm wondering if i'm way off track with this. i want to end up with a full access database storing all of these projects, where each project can have a number of images and files associated with it, and i need to be able to convert it to xml for storage and retrieval on a web server, to display the contents in a php web page.

do i have to use relational tables to do this? if so, is it easy to output all this to xml?

thanks for the help,
dave

[argoSquirrel]

As for each project having a number of images or files, what you are trying to model is a one-to-many relationship: 1 project can have many images or files.  You would want a images table with many images and each image would have a foreign key back to the primary key (project_id) in the projects table.  If you want an image or file to be able to be used on more than 1 project, you would need a different structure.

Also, do you mean actually store the image or file in access?  I have nearly no experience with access, but I'm not sure it can store binary data.  If it can, you may be luck.

As for the xml:

Why exactly are you trying to build a database in xml? Just looking at that xml structure it appears it is ripe for a relational database to do storage and display. You may have technical reasons, but i had to ask.  I'm just not sure what you are doing.

[bullchina]

yeah, well it's becaue i'm not sure what server it's going to go on, but i know it will have php, and i know i can get it to work with xml, but i don't know much else about databases. i see your point about the one to many thing, that makes sense. each image will be used only once, and each project will have a folder where the images and files will be stored, adn the relative location of those folders will be stored as a string in the projects table.

i guess all i need now is to figure out how to connect to the database in php and all that, and i'm sure there are plenty of tutorials out there. thanks for your help,

dave