writing xml data into .txt file

Question

I want to right .txt file for which data is coming from sql database. The text i want to write on .txt file is XML datatype in sql.

I am getting xml data in txt file but its acting as single string.I want it looks like xml format.

 Dim swRMSRequest As StreamWriter
 swRMSRequest = File.CreateText(strFileRMSRequest)
 swRMSRequest.WriteLine(dtRow("RMS_reqSentM"))
 swRMSRequest.Close()

File path is strFileRMSRequest. dtRow("RMS_reqSentM") looks like as below :

I want my text file should looks like

Have you seen [What is the simplest way to get indented XML with line breaks from XmlDocument?](https://stackoverflow.com/questions/203528/what-is-the-simplest-way-to-get-indented-xml-with-line-breaks-from-xmldocument) It's in C#, but the methods used are the same as you'd use in VB.NET. — Andrew Morton, Apr 13 '20 at 15:29

score 0 · Answer 1 · edited Apr 14 '20 at 04:11

Since you're only concerned about a single row's XML use ExecuteScalar to get just your RMS_reqSentM value based on some condition (my example will be where the PK matches a value). Once you get your value, simply call IO.File.WriteAllText to write the value to the text file.

'Declare the object that will be returned from the command
Dim xml As String

'Declare the connection object
Dim con As OleDbConnection

'Wrap code in Try/Catch
Try
    'Set the connection object to a new instance
    con = New SqlConnection()

    'Create a new instance of the command object
    'TODO: Change MyTable and MyTableId to valid values
    Using cmd As SqlCommand = New SqlCommand("SELECT RMS_reqSentM FROM MyTable WHERE MyTableId = @0;", con)
        'Paramterize the query
        'TODO: Change MyId to a valid value
        cmd.Parameters.AddWithValue("@0", MyId)

        'Open the connection
        con.Open()

        'Use ExecuteScalar to return a single value
        xml = cmd.ExecuteScalar()

        'Close the connection
        con.Close()
    End Using
Catch ex As Exception
    'Display the error
    Console.WriteLine(ex.Message)
Finally
    'Check if the connection object was initialized
    If con IsNot Nothing Then
        If con.State = ConnectionState.Open Then
            'Close the connection if it was left open(exception thrown)
            con.Close()
        End If

        'Dispose of the connection object
        con.Dispose()
    End If
End Try

If (Not String.IsNullOrWhitespace(xml)) Then
    'Write the XML to the text file
    IO.File.WriteAllText(strFileRMSRequest, xml)
End If

Please very wary using Parameters.AddWithValue, especially in situations where you might have an invaild type cast trip you. Always much better to use Parameters.Add("@Name", DbType) — Hursey, Apr 13 '20 at 22:29

Yitzhak Khabinsky · Answer 2 · 2020-04-13T17:08:39.970

0

You need to treat your text file as XML. LINQ to XML is a best API to do that.

VB.NET

Sub Main
    Dim strXMLFile As String = "e:\temp\temp.xml"
    Dim xmlDoc As XDocument = XDocument.Parse(dtRow("RMS_reqSentM").ToString)

    Dim Settings As New XmlWriterSettings()
    Settings.Indent = True            ' make it False if you want XML as one single line
    Settings.OmitXmlDeclaration = True  ' Suppress the xml header <?xml version="1.0" encoding="utf-8"?>
    Settings.Encoding = New System.Text.UTF8Encoding(False) ' The false means, do not emit the BOM.

    Dim Writer As XmlWriter = XmlWriter.Create(strXMLFile, Settings)

    xmlDoc.Save(Writer)
End Sub

edited Apr 13 '20 at 17:08

answered Apr 13 '20 at 15:30

Yitzhak Khabinsky

18,471
2
15
21

its creating blank .xml file – spujaname Apr 13 '20 at 16:22
`XDocument.Parse()` call needs its parameter as a string. So `dtRow("RMS_reqSentM").ToString` might help. – Yitzhak Khabinsky Apr 13 '20 at 17:07

score 0 · Answer 3 · answered Apr 23 '20 at 21:49

This is how i get it done.Hope that help others.

Protected Sub GetTextFiles(ByVal strPath As String, ByVal strmessage As String, 
ByVal reqType As String)


    Try
        If (reqType = "XML") Then
            Dim swRMSRequest As StreamWriter
            Dim txtstr As String = String.Empty
            'swRMSRequest = File.CreateText(strFileRMSRequest)
            swRMSRequest = File.CreateText(strPath)
            Dim xmldoc As XmlDocument = New XmlDocument()
            'xmldoc.LoadXml(dtRow("RMS_reqSentM"))
            xmldoc.LoadXml(strmessage)
            Dim sb As New StringBuilder()
            ''`'We will use stringWriter to push the formated xml into our StringBuilder sb.`  
            Using stringWriter As New StringWriter(sb)
                '' `'We will use the Formatting of our xmlTextWriter to provide our indentation.`  
                Using xmlTextWriter As New XmlTextWriter(stringWriter)
                    xmlTextWriter.Formatting = Formatting.Indented
                    xmldoc.WriteTo(xmlTextWriter)
                End Using
                txtstr = sb.ToString()
            End Using
            swRMSRequest.WriteLine(txtstr)
            swRMSRequest.WriteLine(" ")
            swRMSRequest.Close()
        Else
            Dim swRMSRequest As StreamWriter
            Dim txtstr As String = strmessage
            swRMSRequest = File.CreateText(strPath)
            Dim sb As New StringBuilder()
            swRMSRequest.WriteLine(txtstr)
            swRMSRequest.WriteLine(" ")
            swRMSRequest.Close()
        End If


    Catch ex As Exception
        ex.Message.ToString()
    End Try
End Sub`

writing xml data into .txt file

3 Answers3