Solved

Reading XML with VB.NET

Posted on 2012-03-23
8
520 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
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

827 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