none
Reading XML and write it into database RRS feed

  • Question

  •  

    Hi,

     

    i want to read a xml file into a sql database table. There 's no problem reading the xml into a dataset using the following code:

     

    Code Snippet

    DataSet xmlDS = new DataSet();

     

    xmlDS.ReadXml("hb_test1.xml");

     

     

    Now a can display the dta in a dataGridview:

     

    Code Snippet

    this.dataGridView1.DataSource = xmlDS.Tables[0];

    this.dataGridView2.DataSource = xmlDS.Tables[1];

    this.dataGridView3.DataSource = xmlDS.Tables[2];

    this.dataGridView4.DataSource = xmlDS.Tables[3];

     

     

     

    This works fine...

     

    But now i have to write this data into a sql database and i have no idea how to do this.

     

    Please help...

     

    Regards

     

    Thomas

    Monday, April 28, 2008 3:01 PM

Answers

  • You can import XML directly into SQL Server, see this article for some good tips:

     

    http://msdn2.microsoft.com/en-us/library/ms191184.aspx

     

    Here is a simple example:

     

    create table foo (f1 xml)

     

    insert into foo (f1)
    select * from openrowset(
       bulk 'c:\foo.xml',
       single_blob) as x

     

    This of course means your XML file must be on same machine as SQL Server.

     

    If you want to do this remotely, you can use the DataAdapter method.  Something like below:

     

     

    Code Snippet

    using (SqlConnection conn = new SqlConnection("server=MySqlServer;integrated security=sspi;"))

    {

    conn.Open();

    xsql_noerror(conn, "drop table tempXML");

    xsql(conn, "create table tempXML(f1 xml)");

    using (SqlCommand cmd = conn.CreateCommand())

    {

    cmd.CommandText = "insert into tempXml (f1) values (@p1)";

    cmd.Parameters.Add("@p1",SqlDbType.Xml);

    StreamReader sr = new StreamReader(@"c:\foo.xml", Encoding.ASCII, false);

    cmd.Parameters[0].Value = sr.ReadToEnd();

    cmd.ExecuteNonQuery();

    }

    conn.Close();

    }

     

     

    Monday, April 28, 2008 7:41 PM

All replies

  • Since you are talking about DataSet, i would recommend that you use a DataAdapter .. Apart from the MSDN documentation you will find a lot of useful resources if you perform a websearch...
    Monday, April 28, 2008 4:01 PM
  • You can import XML directly into SQL Server, see this article for some good tips:

     

    http://msdn2.microsoft.com/en-us/library/ms191184.aspx

     

    Here is a simple example:

     

    create table foo (f1 xml)

     

    insert into foo (f1)
    select * from openrowset(
       bulk 'c:\foo.xml',
       single_blob) as x

     

    This of course means your XML file must be on same machine as SQL Server.

     

    If you want to do this remotely, you can use the DataAdapter method.  Something like below:

     

     

    Code Snippet

    using (SqlConnection conn = new SqlConnection("server=MySqlServer;integrated security=sspi;"))

    {

    conn.Open();

    xsql_noerror(conn, "drop table tempXML");

    xsql(conn, "create table tempXML(f1 xml)");

    using (SqlCommand cmd = conn.CreateCommand())

    {

    cmd.CommandText = "insert into tempXml (f1) values (@p1)";

    cmd.Parameters.Add("@p1",SqlDbType.Xml);

    StreamReader sr = new StreamReader(@"c:\foo.xml", Encoding.ASCII, false);

    cmd.Parameters[0].Value = sr.ReadToEnd();

    cmd.ExecuteNonQuery();

    }

    conn.Close();

    }

     

     

    Monday, April 28, 2008 7:41 PM