Link to home
Start Free TrialLog in
Avatar of dastaub
dastaubFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dastaub

ASKER

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.