Solved

Reading XML with VB.NET

Posted on 2012-03-23
8
522 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
Technology Partners: 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

691 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