troubleshooting Question

Parse a complex xml document vb.net

Avatar of dkpepsict
dkpepsict asked on
XMLVisual Basic.NET
29 Comments1 Solution994 ViewsLast Modified:
I am trying to parse a bit more complex xml document than usual in my vb.net application, and I just don’t know how to get out the data of the xml which I need to insert into my SQL database.
I just need to extract the data and have it in a DataTable or something I can work with so that I can insert the data into my sql database.
Here is an example of the xml document:
<?xml version="1.0" encoding="UTF-8"?>
<fs_response status="success" timestamp="2012-02-07 22:53:43">
  <headings>
    <heading for="result_status">Result Status</heading>
    <heading for="date_start">Start Time</heading>
    <heading for="date_finish">Finish Time</heading>
    <heading for="date_update">Update Time</heading>
    <heading for="user_ip">User's IP Address</heading>
    <heading for="user_browser">User's Browser</heading>
    <heading for="user_os">User's Operating System</heading>
    <heading for="user_referrer">User's Referring URL</heading>
    <heading for="payment_status">Payment Status</heading>
    <heading for="payment_amount">Payment Amount Paid</heading>
    <heading for="4">Firstname</heading>
    <heading for="5">Lastname</heading>
    <heading for="6">Address</heading>
    <heading for="8">City</heading>
    <heading for="10">ZipCode</heading>
  </headings>
  <results>
    <result id="6284420">
      <metas>
        <meta id="result_status">Complete</meta>
        <meta id="date_start">2012-02-04 10:35:00</meta>
        <meta id="date_finish">2012-02-04 10:44:00</meta>
        <meta id="date_update">2012-02-04 10:44:00</meta>
        <meta id="user_ip">8.8.8.8</meta>
        <meta id="user_browser">Firefox</meta>
        <meta id="user_os">Mac</meta>
        <meta id="user_referrer">http://urladdress</meta>
        <meta id="payment_amount">0.00</meta>
      </metas>
      <items>
        <item id="4" index="4" type="text">
          <value>John</value>
        </item>
        <item id="5" index="5" type="text">
          <value>Doe</value>
        </item>
        <item id="6" index="6" type="text">
          <value>Somestreet 1</value>
        </item>
        <item id="8" index="7" type="text">
          <value>Longville</value>
        </item>
        <item id="10" index="8" type="text">
          <value>6000</value>
        </item>
      </items>
    </result>
    <result id="6282110">
      <metas>
        <meta id="result_status">Complete</meta>
        <meta id="date_start">2012-02-06 19:18:17</meta>
        <meta id="date_finish">2012-02-06 19:28:27</meta>
        <meta id="date_update">2012-02-06 19:28:27</meta>
        <meta id="user_ip">8.8.8.8</meta>
        <meta id="user_browser">Chrome</meta>
        <meta id="user_os">Windows</meta>
        <meta id="user_referrer">http://simeurl</meta>
        <meta id="payment_amount">0.00</meta>
      </metas>
      <items>
        <item id="4" index="4" type="text">
          <value>Jane</value>
        </item>
        <item id="5" index="5" type="text">
          <value>Doe</value>
        </item>
        <item id="6" index="6" type="text">
          <value>Somestreet 2</value>
        </item>
        <item id="8" index="7" type="text">
          <value>Smallville</value>
        </item>
        <item id="10" index="8" type="text">
          <value>7000</value>
        </item>
      </items>
    </result>
  </results>
</fs_response>


This is just a small sample of how the structure is, I have a lot more <heading> tags with generic numbers in the “for” attribute.
For each <result> in <results> I need the ID attribute content of the <result> tag and all meta data and all of the content of <item><value>, where I need to know which heading it relates to.
So at the end I should end up with two tables in my SQL database.
One that contains the <meta> data and the id attribute of the result, and one table with the items content with columns that corresponding to the generic <heading> tags and also the id attribute of the <result>.
The heading will be a constant, so it will not grow than what I have at the moment.

So how can I approach parsing this xml document?
I hope somebody can give me some hints as I have no idea where to start from, where the end result is that has that my SQL database has those two tables filled with data from that xml document.
ASKER CERTIFIED SOLUTION
nepaluz

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 29 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 29 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros