title: Creating MySQL tables from XML data
body: <p>I'm working with a set of lobbying disclosure records. The Secretary of the Senate <a href="http://www.senate.gov/legislative/Public_Disclosure/contributions_download.htm" rel="nofollow">publishes</a> these records as XML files, which look like this:</p>
  
  <pre><code>&lt;Filing ID="1ED696B6-B096-4591-9181-DA083921CD19" Year="2010" Received="2011-01-01T11:33:29.330" Amount="" Type="LD-203 YEAR-END REPORT" Period="Year-End (July 1 - Dec 31)"&gt;
  &lt;Registrant xmlns="" RegistrantID="8772" RegistrantName="CERIDIAN CORPORATION" Address="4524 Cheltenham Drive&amp;#13;&amp;#10;Bethesda, MD 20814" RegistrantCountry="USA"/&gt;
      &lt;Lobbyist xmlns="" LobbyistName="O'CONNELL, JAMES"/&gt;
  &lt;/Filing&gt;
  
  &lt;Filing ID="179345CF-8D41-4C71-9C19-F41EB88254B5" Year="2010" Received="2011-01-01T13:48:31.543" Amount="" Type="LD-203 YEAR-END AMENDMENT" Period="Year-End (July 1 - Dec 31)"&gt;
      &lt;Registrant xmlns="" RegistrantID="400447142" RegistrantName="Edward Merlis" Address="8202 Hunting Hill Lane&amp;#13;&amp;#10;McLean, VA 22102" RegistrantCountry="USA"/&gt;
      &lt;Lobbyist xmlns="" LobbyistName="Merlis, Edward A"/&gt;
      &lt;Contributions&gt;
          &lt;Contribution xmlns="" Contributor="Merlis, Edward A" ContributionType="FECA" Payee="DeFazio for Congress" Honoree="Cong. Peter DeFazio" Amount="250.0000" ContributionDate="2010-09-05T00:00:00"/&gt;
          &lt;Contribution xmlns="" Contributor="Merlis, Edward A" ContributionType="FECA" Payee="Friends of Jim Oberstar" Honoree="Cong. Jim Oberstar" Amount="1000.0000" ContributionDate="2010-09-01T00:00:00"/&gt;
          &lt;Contribution xmlns="" Contributor="Merlis, Edward A" ContributionType="FECA" Payee="McCaskill for Missouri 2012" Honoree="Senator Claire McCaskill" Amount="1000.0000" ContributionDate="2010-09-18T00:00:00"/&gt;
          &lt;Contribution xmlns="" Contributor="Merlis, Edward A" ContributionType="FECA" Payee="Mesabi Fund" Honoree="Cong. Jim Oberstar" Amount="500.0000" ContributionDate="2010-07-13T00:00:00"/&gt;
      &lt;/Contributions&gt;
  &lt;/Filing&gt;
  </code></pre>
  
  <p>As you can see, some <code>&lt;Filing&gt;</code> tags also contain <code>&lt;Contribution&gt;</code> tags, but others do not.</p>
  
  <p>I see two objects here: contributors (i.e., lobbyists) and contributions (i.e., a transaction between a lobbyist and a member of Congress).</p>
  
  <p>I'd like to load these records into a MySQL database. To me, the logical structure would include two tables: one for contributors (with fields for name, ID, address, etc.) and one for contributions (with amount, recipient, etc., and a relational link to the list of contributors).</p>
  
  <p>My question: am I approaching this problem correctly? If so, does this data schema make sense? Finally, how am I to parse the XML to load it into the MySQL tables as I've structured them?</p>