Link to home
Start Free TrialLog in
Avatar of Muhammad Kashif
Muhammad KashifFlag for Pakistan

asked on

How I can insert XML database (including multiple tables) into SQL server 2005 using VB.NET 2.0

Hi,
I have an XML file, including multiple tables and their records.
How can I insert the XML file into SQL server 2005 using VB.NET.
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

do you want to insert the xml in a column or create the database from the xml file?
Avatar of Muhammad Kashif

ASKER

No, I want to Insert all the XML data into SQL server 2005 database.
The XML and SQL server database 2005 as same schema
The data in XML file is like that
-
+
  1
  23
  2008-06-27T00:00:00+05:00
  ghngg
  ghgh
  hgggf
  1
  fghgfhg
  2222
  23
  5'6''
  500
  37
  23
  180/200
  o.k
  ok
  ok
  o.k
  o.k
  o.k
  o.k
  o.k
  o.k
  o.k
  o.k
  false
 
+
  1
  235
  2008-06-07T00:00:00+05:00
  Muhammad Kashif
  321564987
  Test
  1
  ABC
  21
  30
  5'5''
  50.5
  98
  80
  110 088
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NIL
  NIL
  NIL
  false
 
+
  1
  235
  2008-06-07T00:00:00+05:00
  Muhammad Kashif
  321564987
  Test
  2
  XYZ
  23654
  35
  5'6''
  75
  98
  82
  100 110
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NORMAL
  NIL
  NIL
  NIL
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  1
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  2
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  3
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  4
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  5
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
+
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  6
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
-
  1
  456
  2008-07-04T00:00:00+05:00
  Doc.
  MLN 1234
  IPBA
  7
   
   
  0
   
  0
  0
  0
   
   
   
   
   
   
   
   
   
   
   
   
  false
 
________________________________

There are many tables in XML file like HEALTH_ExaminationRec with their records
you could load the xml in a a dataset like in the code below

this dataset contains all the tables and their data which you may manipulate further.
Dim l_strXMLfile as String = "C:\Test\Example.xml"
Dim l_dsMyDataSet As New DataSet
 
l_dsMyDataSet.ReadXml(l_strXMLfile)

Open in new window

In the next example dsSQLDataSet is your SQLDataSet and daHEALTH_ExaminationRec is the DataAdapter for the specific table
Dim l_drRow as DataRow
For Each l_drRow in l_dsMyDataSet.Tables("HEALTH_ExaminationRec").Rows
     dsSQLDataSet.Tables("HEALTH_ExaminationRec").Add(l_drRow)
     daHEALTH_ExaminationRec.Update(dsSQLDataSet)
Next

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Muhammad Kashif
Muhammad Kashif
Flag of Pakistan 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