?
Solved

Reading XML with VB.NET

Posted on 2012-03-23
8
Medium Priority
?
528 Views
Last Modified: 2012-03-24
Hello all,

Im calling CJ API and getting an XML as output, which I store in a variable called OUTPUT. This is my code:

 Dim myHttpWebRequest As System.Net.HttpWebRequest = CType(System.Net.WebRequest.Create("https://commission-detail.api.cj.com/v3/commissions?date-type=posting&start-date=2012-3-1&end-date=2012-3-31"), System.Net.HttpWebRequest)
        Dim myWebHeaderCollection As System.Net.WebHeaderCollection = myHttpWebRequest.Headers
        myWebHeaderCollection.Add("authorization:<My_Key_which_I_dont_display")
        Dim output As String = ""
        Using myHttpWebResponse As System.Net.HttpWebResponse = CType(myHttpWebRequest.GetResponse(), System.Net.HttpWebResponse)
            Dim responseReader As System.IO.StreamReader = New System.IO.StreamReader(myHttpWebResponse.GetResponseStream(), Encoding.UTF8)
            output = responseReader.ReadToEnd()
            responseReader.Close()
        End Using


I get the following HTML as a response:

<?xml version="1.0" encoding="UTF-8"?> <cj-api><commissions total-matched="2"><commission><action-status>new</action-status><action-type>advanced sale</action-type><aid>10769812</aid><commission-id>1374746951</commission-id><country></country><event-date>2012-03-07T15:36:47-0800</event-date><locking-date>2012-04-10</locking-date><order-id>3423423442</order-id><original>true</original><original-action-id>1130909425</original-action-id><posting-date>2012-03-07T16:31:28-0800</posting-date><website-id>2342343</website-id><action-tracker-id>4234234432</action-tracker-id><action-tracker-name>Existing User</action-tracker-name><cid>4234234432</cid><advertiser-name>Groupon</advertiser-name><commission-amount>0.50</commission-amount><order-discount>0.00</order-discount><sid></sid><sale-amount>25.00</sale-amount></commission><commission><action-status>new</action-status><action-type>advanced sale</action-type><aid>10769812</aid><commission-id>5345435345</commission-id><country>US</country><event-date>2012-03-11T09:27:55-0700</e
vent-date><locking-date>2012-04-10</locking-date><order-id>53434345</order-id><original>true</original><original-action-id>654543534</original-action-id><posting-date>2012-03-11T10:30:19-0700</posting-date><website-id>5587897</website-id><action-tracker-id>337064</action-tracker-id><action-tracker-name>Existing User</action-tracker-name><cid>646546465</cid><advertiser-name>Groupon</advertiser-name><commission-amount>0.06</commission-amount><order-discount>0.00</order-discount><sid></sid><sale-amount>6.00</sale-amount></commission></commissions></cj-api>

How do I read each field of the XML thru VB.NET?

Thanks!
0
Comment
Question by:pvg1975
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37759744
What do you want to do with each field (aside from reading it)?
0
 

Author Comment

by:pvg1975
ID: 37759923
Store it on a database
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37760423
First, you need to parse the output, thus
Dim CJResponse = XElement.Parse(output)

Open in new window

If you do not already have the names of each tag under the commission tag, you can get them by:
Dim Tags = From x In CJResponse.Descendants...<commission> Select x.Descendants
Dim xTags = (From x In Tags.FirstOrDefault Select x.Name.LocalName).ToList

Open in new window

The xTags gives the names:
tag namesYou then need to add these to your database. Unfortunately, you have not said what database you want these put to, not have you said what columns / tags you want to put in the database.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pvg1975
ID: 37760795
Thanks!

The DB is SQL Server, Im ok with placing the records on the DB.

My question is, now do I navigate the XML results?

Like

For X=1 to Num_Records_on_XML
    Commission_ID={Commission ID record on XML}
    ORder_ID={order ID record on XML}
    etc...
Next
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37760826
This should give you the results
        Dim Tags = From x In CJResponse.Descendants...<commission> Select x.Descendants
        Dim xTags = (From x In Tags.FirstOrDefault Select x.Name.LocalName).ToList
        Dim ResultList As New List(Of Dictionary(Of String, String))
        For Each x In Tags
            Dim Res1 = (From q In x Select q.Value).ToList
            Dim xRes As New Dictionary(Of String, String)
            For Each y In xTags
                xRes.Add(y, Res1.Item(xTags.IndexOf(y)))
            Next
            ResultList.Add(xRes)
        Next

Open in new window

ResultList has 4 dictionaries (from your given response) that contains the values of each commission tag. All you need to do to place them in a variable is loop through the dictionaries and retrieve the item values
0
 

Author Comment

by:pvg1975
ID: 37760967
Thanks Nepaluz!

I see, but is there a way to navigate trough the records instead of the elements?

I basically need to have access to the following records on the loop and I cannot figure out the values for the following records only:

advertiser-name
order-id
event-date
sale-amount
commission-amount

Those are the only fields I need. When I use your code above, it seems like it is looping through all the elements and I dont know how to identify the field names or when a new record is been read (instead of a list of elements).

I need something like:

For X =1 to EOF of XML
  advertiser-name=advertiser-name on XML
  order-id=order-id on XML
  event-date = event-date on XML
  sale-amount= sale-amount on XML
  commission-amount=commission-amount on XML
  Then I Save the variables above on database (this is fine, I can do it)
Next

Thanks again :)

Paula.
0
 
LVL 17

Accepted Solution

by:
nepaluz earned 2000 total points
ID: 37761002
You only need to make a small change to the code. Add thefields you want to an array, and if the tag is inthe array, add that to the dictionary. Finally, iterate through the results and populate your database.
        Dim Tags = From x In CJResponse.Descendants...<commission> Select x.Descendants
        Dim xTags = (From x In Tags.FirstOrDefault Select x.Name.LocalName).ToList
        Dim ResultList As New List(Of Dictionary(Of String, String))
        For Each x In Tags
            Dim Res1 = (From q In x Select q.Value).ToList
            Dim xRes As New Dictionary(Of String, String)
            For Each y In xTags
                If {"advertiser-name", "order-id", "event-date", "sale-amount", "commission-amount"}.Contains(y) Then
                    xRes.Add(y, Res1.Item(xTags.IndexOf(y)))
                End If
            Next
            ResultList.Add(xRes)
        Next


        For Each q In ResultList
            Dim advertiser_name = q.Item("advertiser-name")
            Dim order_id = q.Item("order-id")
            Dim event_date = q.Item("event-date")
            Dim sale_amount = q.Item("sale-amount")
            Dim commission_amount = q.Item("commission-amount")
            'here add to your database
        Next

Open in new window


res
0
 

Author Closing Comment

by:pvg1975
ID: 37761092
That is AWESOME!!! If I could give you 10,000 points, I would do it right now!!!

Thanks A LOT for your Help :)))))

Paula.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 10 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question