Writing multiple SQL server tables as separate nodes to XML Document

Question

I have multiple SQL server tables from which I want to pull data and write to XML. I want my XML to be formed like this:

 <Data>
     <Query1Table>
          <Table>
              <Column1>Data</Column1>
              <Column2>Data</Column2>
              ...
          </Table>
      </Query1Table>
      <Query2Table>
          <Table>
              <Column1>Data</Column1>
              <Column2>Data</Column2>
              ...
          </Table>
      </Query2Table>
   </Data>

I'm using datasets to write the xml, but the code I'm working with doesn't append the data, it overwrites:

        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim directory As String
        Dim ds As New DataSet
        Dim sql As String

        connection = New SqlConnection(connetionString)
        sql = "select * from scheduledata"
        connection.Open()
        adapter = New SqlDataAdapter(sql, connection)
        adapter.Fill(ds)

        ds.DataSetName = "Schedule"
        ds.WriteXml(directory)

        ds.Clear()
        sql = "select * from costdata"
        adapter = New SqlDataAdapter(sql, connection)
        adapter.Fill(ds)
        ds.WriteXml(directory)

I have tried adding it all to the same data set by calling the SQL queries at once, but that doesn't help to separate them in the XML -- it groups them in the same node.

I'm open to a different method if anyone has a good suggestion.

Answer 1

The way that I write to .xml files (which works) is as follows

Private Sub AuthenticationContinuebtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthenticationContinuebtn.Click

    On Error GoTo PasswordHandler

    GlobalVariables.Username = Usernametxtbx.Text
    GlobalVariables.Password = Passwordtxtbx.Text
    GetUsernamePassword()
    Close()
    Exit Sub
    PasswordHandler:
    MsgBox("Incorrect password or username.")
End Sub


Private Sub UserAuthenticationWindow_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim FileName4 As String = "C:\Forte\UsernamePassword.xml"
    Dim FileRead4 As XmlTextReader = New XmlTextReader("C:\Forte\UsernamePassword.xml")

    'If statement to see if file exists.
    If System.IO.File.Exists(FileName4) = True Then

        Do While (FileRead4.Read)
            Select Case FileRead4.NodeType

                Case XmlNodeType.Text, XmlNodeType.Element

                    If FileRead4.Name = "Username" Then
                        FileRead4.Read()
                        Usernametxtbx.Text = FileRead4.Value
                        GlobalVariables.Username = Usernametxtbx.Text
                    End If
                    If FileRead4.Name = "Password" Then
                        FileRead4.Read()
                        Passwordtxtbx.Text = FileRead4.Value
                        GlobalVariables.Password = Passwordtxtbx.Text
                    End If
            End Select
        Loop
    Else
        MainBox.MainTextBox.AppendText(FileName4 & " could not be found. Settings are restored to defaults.")
        MainBox.Logging(Date.Now & FileName4 & " could not be found. Settings are restored to defaults.")
    End If
    FileRead4.Close()
End Sub

Public Sub createNode3(ByVal Username As String, ByVal Password As String, ByVal writer As XmlTextWriter)

    'On Error Resume Next
    writer.WriteStartElement("Username_Password")
    writer.WriteStartElement("Username")
    writer.WriteString(Username)
    writer.WriteEndElement()
    writer.WriteStartElement("Password")
    writer.WriteString(Password)
    writer.WriteEndElement()
    writer.WriteEndElement()

End Sub

Answer 2

You've defined directory as a string, so ds.WriteXml will (create and) write to a file by that name.

Use a FileStream or XmlTextWriter instead.