[Webinar] Streamline your web hosting managementRegister Today

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

VB.net XML output

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
dastaub
Asked:
dastaub
3 Solutions
 
Robert SchuttSoftware EngineerCommented:
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
 
Jacques Bourgeois (James Burger)PresidentCommented:
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
 
Fernando SotoRetiredCommented:
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
 
dastaubAuthor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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