Reading XML with VB.NET

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!
pvg1975Asked:
Who is Participating?
 
nepaluzConnect With a Mentor Commented:
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
 
käµfm³d 👽Commented:
What do you want to do with each field (aside from reading it)?
0
 
pvg1975Author Commented:
Store it on a database
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
nepaluzCommented:
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
 
pvg1975Author Commented:
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
 
nepaluzCommented:
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
 
pvg1975Author Commented:
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
 
pvg1975Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.