locked
XML SOLUTION IN LOT RRS feed

  • Question

  • Hi Guys,

    Anobody knows how can i do:

    I Have a directory with many folders and into this folders exist a lot of xml files with diferent names

    So, I need make an aplication that read this folders/xml´s and after check some xelements

    import/input this datas in a db.table.


    Please if someone saw or know something like this, i´d apreciate some help!

    Thanks a lot!!

    Thursday, March 20, 2008 5:45 PM

Answers

  • Aron,

    Based on what you have posted the most efficient way to accomplish this would be to create a program that used the folder path to read all the xml files and insert this into sql based on your specific needs, "I need make an aplication that read this folders/xml´s and after check some xelements".  The code snippet is a windows application that has a single textbox and button.  The text box is for the path of the files and the button click event begins the process of iterating through each file in the path and passing each file to a sub that will read the xml document into a variable and then insert this into a table with an xml column.

     

    Hope this helps

     

    Code Snippet

    Imports System.Data.SqlClient

    Imports System.Xml

    Imports System.IO

    Imports System.Text.RegularExpressions

     

     

    Public Class Form1

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click

    Dim strFile As String

    Dim nm As String

    Dim fpath As String

    fpath = txtPath.Text

    Try

    If fpath.Substring(fpath.Length - 1, 1) <> "\" Then

    fpath = fpath.ToString + "\"

    End If

    'Assure that the directory exists before searching the file objects

    If Directory.Exists(fpath) Then

    For Each strFile In Directory.GetFiles(fpath)

    Dim fi As New FileInfo(strFile)

    If fi.Extension.ToString = ".xml" Then

    nm = fi.ToString

    ImportFile(nm)

    End If

    Next

    End If

    Catch ex As Exception

    End Try

     

    End Sub

    Private Sub ImportFile(ByVal nm As String)

    Dim sContents As String

    Dim sr As New StreamReader(nm)

    sContents = sr.ReadToEnd

    sr.Close()

     

    'Put your code here to validate the xml doc

    'Replace singlequotations so that insert is successful

    sContents = sContents.Replace("'", "''")

    Dim cn As New SqlConnection("Data Source=pd1;integrated security=sspi;" & "initial Catalog=Pubs;")

    Dim cmd As New SqlCommand("INSERT temp VALUES('" & sContents & _

    "')", cn)

    Try

    cn.Open()

    cmd.ExecuteNonQuery()

    Catch ex As Exception

    End Try

     

    End Sub

    End Class

     

     

     

    Friday, March 21, 2008 1:54 PM

All replies

  • Aron,

    Based on what you have posted the most efficient way to accomplish this would be to create a program that used the folder path to read all the xml files and insert this into sql based on your specific needs, "I need make an aplication that read this folders/xml´s and after check some xelements".  The code snippet is a windows application that has a single textbox and button.  The text box is for the path of the files and the button click event begins the process of iterating through each file in the path and passing each file to a sub that will read the xml document into a variable and then insert this into a table with an xml column.

     

    Hope this helps

     

    Code Snippet

    Imports System.Data.SqlClient

    Imports System.Xml

    Imports System.IO

    Imports System.Text.RegularExpressions

     

     

    Public Class Form1

    Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click

    Dim strFile As String

    Dim nm As String

    Dim fpath As String

    fpath = txtPath.Text

    Try

    If fpath.Substring(fpath.Length - 1, 1) <> "\" Then

    fpath = fpath.ToString + "\"

    End If

    'Assure that the directory exists before searching the file objects

    If Directory.Exists(fpath) Then

    For Each strFile In Directory.GetFiles(fpath)

    Dim fi As New FileInfo(strFile)

    If fi.Extension.ToString = ".xml" Then

    nm = fi.ToString

    ImportFile(nm)

    End If

    Next

    End If

    Catch ex As Exception

    End Try

     

    End Sub

    Private Sub ImportFile(ByVal nm As String)

    Dim sContents As String

    Dim sr As New StreamReader(nm)

    sContents = sr.ReadToEnd

    sr.Close()

     

    'Put your code here to validate the xml doc

    'Replace singlequotations so that insert is successful

    sContents = sContents.Replace("'", "''")

    Dim cn As New SqlConnection("Data Source=pd1;integrated security=sspi;" & "initial Catalog=Pubs;")

    Dim cmd As New SqlCommand("INSERT temp VALUES('" & sContents & _

    "')", cn)

    Try

    cn.Open()

    cmd.ExecuteNonQuery()

    Catch ex As Exception

    End Try

     

    End Sub

    End Class

     

     

     

    Friday, March 21, 2008 1:54 PM

  • Hi derek, thanks for help, but i think that i didn´t explane my problem clearly!

    So let´s see

    I need check all nodes on each others xml files because i just need some informmatiosn on this nodes

    there are 5 typesof informations and i need check and input in diferent tables likse this:


    <mdc xmlns:HTML="http://www.w3.org/TR/REC-xml">
    <mfh>
    <ffv>32.401 V6.2</ffv>
    <sn>SubNetwork=ONRM_RootMo_R,SubNetwork=RNCRJ01,MeContext=RNCRJ01</sn>
    <st></st>
    <vn></vn>
    <cbt>20080202020000Z</cbt>
    </mfh>
    <md>
        <neid>
            <neun>RNCRJ01</neun>
            <nedn>SubNetwork=ONRM_RootMo_R,SubNetwork=RNCRJ01,MeContext=RNCRJ01</nedn>
            <nesw>CXP9012014_R10BE</nesw>
        </neid>
        <mi>
            <mts>20080202021500Z</mts>
            <gp>900</gp>
            <mt>pmReceivedAtmCells</mt>
            <mt>pmTransmittedAtmCells</mt>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=MS-27-1,VplTp=vp101</moid>
                <r>382017416</r>
                <r>383278974</r>
            </mv>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=MS-6-1,VplTp=vp101</moid>
                <r>389817961</r>
                <r>385601654</r>
            </mv>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=ES-1-27-1,VplTp=vp1</moid>
                <r>199325767</r>
                <r>1156328886</r>
            </mv>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=ES-1-2-1,VplTp=vp1</moid>
                <r>103661422</r>
                <r>91363574</r>
            </mv>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=MS-27-1,VplTp=vp1</moid>
                <r>493041455</r>
                <r>475817680</r>
            </mv>
            <mv>
                <moid>ManagedElement=1,TransportNetwork=1,AtmPort=MS-6-1,VplTp=vp1</moid>
                <r>405920839</r>
                <r>395560831</r>
            </mv>
        </mi>
    </md>
    </mdc>

     

    So I many files like this above and

     

    in <mi> node i have the <mt> informations that i need compare with some variables if this check ok i need

    take the <mv> informations and insert on table especific to pmReceivedAtmCells and pmTransmittedAtmCells
    in this <mi> node i have 6 <mv> nodes and a i need make a loop to insert this informations on the table.

     

    something like these:

     

      For Each dados In root.<md>.<mi>
                    If dados.<mt>.Nodes.Count = 3 Then
                        If dados.<mt>(0).Value = "pmReceivedAtmCells" And dados.<mt>(1).Value = "pmTransmittedAtmCells" Then
                            Dim result As String = _
                            (From mv In root.<md>(0).<mi>(0).<mv> _
                            Let data = mv.<moid>(0).Value.Split(",") _
                            Let a = data(2).Split("=")(1) _
                            Let b = data(3).Substring(8) _
                            Let neun = root.<md>(0).<neid>(0).<neun>(0).Value.Substring(3) _
                            Let mts = root.<md>(0).<mi>(0).<mts>(0).Value.Substring(0, 12) _
                            Select "Insert into teste values('" + neun + "','" + mts + "','" + a + "','" + b + "','" + mv.<r>(0).Value + "','" + mv.<r>(1).Value() + "');") _
                            .Aggregate(New StringBuilder(), Function(sb As StringBuilder, s As String) sb.Append(s + Environment.NewLine), Function(sb As StringBuilder) sb.ToString())
                            Dim Inserir As MySqlClient.MySqlCommand = New MySqlCommand(result, Con)
                            Try
                                Inserir.ExecuteReader(CommandBehavior.CloseConnection)
                            Catch ex As Exception
                                MsgBox(ex, MsgBoxStyle.Information)
                            End Try
                        End If
                    End If
                Next

     

    I´m using LINQ to XML!!!

     

     

    And the other problem is that i have diferent <mt> informations that needs to insert in another table

     

     

    I´ll study xml with .net but my course will start only in 05/2008 and i need save my job that depends this solution because i´m here temporally. So if you could help me with some thing i´ll be greatfull.

     

     

    Best Regards,

     

     

     




     

     

    Monday, March 24, 2008 12:03 PM