Question How to export xml.files data into sql server management studio??

darkstrike20

Member
Joined
Jul 17, 2014
Messages
7
Programming Experience
Beginner
I would like to convert a xml file to sql server . The below code enable me to read each node and every value of my xml files and i will keep the parent node into parent array and children node into children array then i would run a sql insert table query , however i can't separate the array into different Staff code such as staffcode = 100 or staffcode = 101 into different array. My objective is to create a windows application where the user input the database name , user , password and table name. Then the app will check each node and value from the xml files and input all of them into sql server. GetElementsByTagName are not usable in my case because I am dealing with many different xml files which have different number of nodes and names. Please ignore the NewDataSet node as well , Mt_Staff is my table name.

'Example of an xml file
VB.NET:
 <?xml version="1.0" standalone="yes" ?> 
[URL="file:///D:/XMLfiles/Mt_Staff.xml#"]-[/URL] <NewDataSet>
[URL="file:///D:/XMLfiles/Mt_Staff.xml#"]-[/URL] <Mt_Staff>
  <StaffCode>100</StaffCode> 

  <StaffName>Agent 100</StaffName> 

  <InActive>0</InActive> 

  <Password>!')RWY!</Password> 

  <AccessLevelNo>3</AccessLevelNo> 

  </Mt_Staff>


[URL="file:///D:/XMLfiles/Mt_Staff.xml#"]-[/URL] <Mt_Staff>
  <StaffCode>101</StaffCode> 

  <StaffName>Agent 101</StaffName> 

  <InActive>0</InActive> 

  <Password>'(&XXW!</Password> 

  <AccessLevelNo>3</AccessLevelNo> 

  </Mt_Staff>


[URL="file:///D:/XMLfiles/Mt_Staff.xml#"]-[/URL] <Mt_Staff>
  <StaffCode>666</StaffCode> 

  <StaffName>Staff</StaffName> 

  <InActive>0</InActive> 

  <Password>(&(XVY)</Password> 

  <AccessLevelNo>0</AccessLevelNo> 

  </Mt_Staff>


[URL="file:///D:/XMLfiles/Mt_Staff.xml#"]-[/URL] <Mt_Staff>
  <StaffCode>888</StaffCode> 

  <StaffName>888</StaffName> 

  <InActive>0</InActive> 

  <Password>)')YWZ!</Password> 

  <AccessLevelNo>0</AccessLevelNo> 

  </Mt_Staff>


  </NewDataSet>





 Select Case reader.NodeType
                Case XmlNodeType.Element 'display beginning of element.
                    parent(i) = reader.Name
                    i += 1
    'MessageBox.Show(parent(i))
                    If reader.HasAttributes Then 'if attributes exist
                        While reader.MoveToNextAttribute()
    'display attribute name and value.
                            Console.WriteLine(" {0}='{1}'", reader.Name, reader.Value)
                        End While
                    End If
                    Console.WriteLine(">")


                Case XmlNodeType.Text 'display the text in each element.
                    child(j) = reader.Value
                    j += 1
                    Console.WriteLine(child(j))
    'MessageBox.Show(child(i))
                Case XmlNodeType.EndElement 'display end of element.
                    Console.Write("</" + reader.Name)
                    Console.WriteLine(">")


            End Select
        Loop
        Console.ReadLine()
 
Last edited by a moderator:
Top Bottom