Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

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!
0
pvg1975
Asked:
pvg1975
  • 4
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
nepaluzCommented:
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now