Solved

VB.net XML output

Posted on 2014-02-12
4
400 Views
Last Modified: 2014-02-13
The below visual basic Sub ProcessPatientVolume()  successfully creates this XML output:

    <Volume>
      <FacNo>183</FacNo>
      <YearMonth>201312</YearMonth>
      <Visits>1</Visits>
      <FacNo>183</FacNo>
      <YearMonth>201401</YearMonth>
      <Visits>1</Visits>
      <FacNo>203</FacNo>
      <YearMonth>201311</YearMonth>
      <Visits>1</Visits>
      <FacNo>203</FacNo>
      <YearMonth>201401</YearMonth>
      <Visits>1</Visits>
    </Volume>

I would like the output to include <Item></Item> as shown below:
    <Volume>
      <Item>
      <FacNo>183</FacNo>
      <YearMonth>201312</YearMonth>
      <Visits>341</Visits>
      </Item>
      <Item>
      <FacNo>183</FacNo>
      <YearMonth>201401</YearMonth>
      <Visits>431</Visits>
      </Item>
    </Volume>

How can I add <Item></Item> to the XML output?

   Private Sub ProcessPatientVolume()
dim xVolume As New XElement("Volume")

          Dim SQL As String = ""
        SQL = "SELECT FacNo, SUBSTRING(TimeIn, 1, 6) AS YearMonth, COUNT(VisitID) AS Visits FROM Visit GROUP BY FacNo, SUBSTRING(TimeIn, 1, 6) ORDER BY FacNo, YearMonth"
        Try
            Using CNN As New SqlConnection(CSCLI)
                CNN.Open()
                Using CMD As New SqlCommand(SQL, CNN)
                    Using DR As SqlDataReader = CMD.ExecuteReader
                        While DR.Read
                            xVolume.Add(New XElement("FacNo", DR.Item("FacNo").ToString))
                            xVolume.Add(New XElement("YearMonth", DR.Item("YearMonth").ToString))
                            xVolume.Add(New XElement("Visits", DR.Item("Visits").ToString))
                        End While
                    End Using
                End Using
            End Using

        Catch ex As Exception
            LOG(ex.ToString)
            ex = Nothing
        End Try

    End Sub
0
Comment
Question by:dastaub
4 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 167 total points
Comment Utility
Change the 3 lines in your While loop to:
                            Dim xItem As New XElement("Item")
                            xItem.Add(New XElement("FacNo", DR.Item("FacNo").ToString))
                            xItem.Add(New XElement("YearMonth", DR.Item("YearMonth").ToString))
                            xItem.Add(New XElement("Visits", DR.Item("Visits").ToString))
                            xVolume.Add(xItem)

Open in new window

0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 167 total points
Comment Utility
While DR.Read
                            Dim xItem As New XElement("Item")
                            xVolume.Add(xItem)
                            xItem .Add(New XElement("FacNo", DR.Item("FacNo").ToString))
                            xItem .Add(New XElement("YearMonth", DR.Item("YearMonth").ToString))
                            xItem .Add(New XElement("Visits", DR.Item("Visits").ToString))
                        End While
0
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 166 total points
Comment Utility
Hi dastaub;

Replace your While with this version and the results should be what you are looking for.
   
While DR.Read
    Dim item As New XElement("Item", _
        New XElement("FacNo", DR.Item("FacNo").ToString), _
        New XElement("YearMonth", DR.Item("YearMonth").ToString), _
        New XElement("Visits", DR.Item("Visits").ToString))
    xVolume.Add(item)
End While
0
 

Author Closing Comment

by:dastaub
Comment Utility
500 points could not be divided equally between 3 answers.  I assume as Computer Science people you would already know that, but I wanted to mention it.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now