Output XML with Nested Loops

[smbarney]

I need to output a table and its related tables to a xml file. I am using output/Print # method to do this. My problem is I need to loop through the primary persons table (tblPersons) selecting records that have been flagged for export (chbExportFlag = True). What I can't figure out is how to loop through and grab the addresses (tblAddress) and school names (tblSchools) associated with each person (one to many on PersonID) and insert them into the correct location with the xml file

Here is what my xml needs to look like:

<Subject>
   <SubjectFirstName>Bob</SubjectFirstName>
   <SubjectLastName>Jones</SubjectLastName>
   <SubjectDOB>03-15-70</SubjectDOB>
<SubjectAddressSection>
        <Address>123 Main St</Address>
        <City>Somewhere</City>
        <Zip>12345</Zip>
</SubjectAddressSection>
<SubjectAddressSection>
        <Address>123 Oak St</Address>
        <City>Somewhere</City>
        <Zip>12345</Zip>
</SubjectAddressSection>
<Education>
        <EducationName>Unversity of NY</EducationName>
</Education>
</Subject>
<Subject>
   <SubjectFirstName>Mary</SubjectFirstName>
   <SubjectLastName>Smith</SubjectLastName>
   <SubjectDOB>12-05-71</SubjectDOB>
<SubjectAddressSection>
        <Address>123 Center St</Address>
        <City>Somewhere</City>
        <Zip>12345</Zip>
    </SubjectAddressSection>
<Education>
        <EducationName>Unversity of NY</EducationName>
        <EducationName>Unversity of Vermont</EducationName>
        <EducationName>Unversity of Maryland</EducationName>
</Education>
</Subject>

How do I nest a loop within a loop? I tried searching for this and can't find anything on it. Thanks for the help.

 

[gemma-the-husky]

you need to build the output file in the same recursive way you iterate the records.

so you get a starting tag, then the nested inside tags, and only then generate the closing tag after the nested tags are completed

ie this sort of skeleton.

for each person
    write opening tag
    generate data within person tags
    write closing tag
next

now, the generate data bit inside will be similar in structure to the person tag, but you will know how to construct this - so you can either explicitly write the code to generate the sub tags, and end up with lots fo little functions to generate the whole xml structure

alternatively, since the data is naturally recursive, you may be able to construct a logically simpler structure.

sort of

sub generatedata
    for each tagpair
         write opening tag
         call generate data 'call this same procedure recursively
         write closing tag
    next
end sub
 
main
establish "root" of xml file
call generatedata

it depends what sort of structure you have, i think.

 

[smbarney]

Where I am connfused on is how to the write the recordset statements for the loop for the Persons table and the nested loops for the addresses and schools.

Here is what I think I need:

Dim MyDB As Database
Dim MyRS As Recordset 'Recordset for Persons Table query
Dim MyRS1 As Recordset 'Recordset for Addresses
Dim MyRS2 As Recordset  'Recordset for Schools
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Set MyDB = CurrentDb

strSQL1 = "SELECT PersonID, FirstName, LastName, DOB, FROM queryPersons"
Set MyRS = MyDB.OpenRecordset(strSQL)

This will get me the records for the first part of my xml. What I am not sure how to do is the nested loops for Addresses and Schools. Would it look like this for the Address table?

strSQL1 = "SELECT AddrressID, PersonID, Address, City, Zip FROM tblAddress Where PersonID = " &  MyRS!PersonID & ""
Set MyRS1 = MyDB.OpenRecordset(strSQL1)

For the actual Output code would it look like this:

Do Until MyRS.EOF
Print #1, "<Subject>
Print #1, "   <SubjectFirstName>MyRS!FirstName</SubjectFirstName>"
Print #1, "   <SubjectLastName>MyRS!LastName</SubjectLastName>"
Print #1, "   <SubjectDOB>MyRS!DOB</SubjectDOB>"

Do Until MyRS1.EOF
Print #1, "<SubjectAddressSection>"
Print #1, "        <Address>MyRS1!Address</Address>"
Print #1, "        <City>MyRS1!City</City>"
Print #1, "        <Zip>MyRS1!Zip</Zip>"
Print #1, "</SubjectAddressSection>"
MyRS1.MoveNext
Loop

Print #1, "</Subject>"
MyRS.MoveNext
Loop

MyRS1.Close
MyRS.Close
Set MyRS1 = Nothing
Set MyRS = Nothing

Thanks