Saturday, 26 April 2014

Insert XML into SQL Server Table using SSMS and C#

Description:

  In SQL Server Relational Database Management System (RDMS), we always try to create best Database Table design. So we can easily avoid XML column by creating equivalent Table design. Querying XMl column by using XQuery will give performance overhead compared with normal T-SQL query. But in complex database design who have most dynamic need, we cannot avoid XML column. In this article, I am going write different examples to Insert XML Data into SQL Server Table using SSMS and .NET C# code.

Summmary:


Insert XML into SQL Table using SQL Server Management Studio

You can easily Insert XML data into SQL Table by using following SQL Insert query.
Use [MorganDB]

Create Table [MyTable] ( ID int, [myXmlColumn] xml);

Insert Into [MyTable] (ID,[myXmlColumn]) 
   Values(1,'<XmlRoot><childNode></childNode></XmlRoot>')

Select * From [MyTable]

Insert XML value into SQL Table using SSMS and .NET C#


Insert XML into SQL Table using SQL Parameter in .NET C#

If you have small size XML value, you can easily Insert XML data into SQL Table by using above SQL Query. But when it becomes large size XML file or XML data, it is very difficult and performance overhead to insert multiple XML records into SQL Table. In that case, we need go for alternative way. So we can use SQL Parameter approach to Insert Multiple XMl records into SQL table.
public static void InsertXMlDataIntoTableBySQLParameter()
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [MyTable] ( ID int, [myXmlColumn] xml)";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        string xmlData = "<XmlRoot><childNode></childNode></XmlRoot>";

        string insertXmlQuery = @"Insert Into [MyTable] (ID,[myXmlColumn]) Values(1,@myXmlColumn)";

      // Insert XMl Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@myXmlColumn", xmlData);
        sqlParam.DbType = DbType.Xml;
        insertCommand.ExecuteNonQuery();
    }
}

Insert XML Value into SQL Table using SQL Query in .NET C#

Use the below example to Insert XML value into SQL Server Table using SQL Query itself without SQL Parameter. This method is recommended only for small size XML data and small number of rows insertions at the same time.
public static void InsertXMlDataIntoTable()
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
        Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        //string createTableQuery = @"Create Table [MyTable] ( ID int, [myXmlColumn] xml)";
        //SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        //command.ExecuteNonQuery();

        string insertXmlQuery = @"Insert Into [MyTable] (ID,[myXmlColumn])
                    Values(1,'<XmlRoot><childNode></childNode></XmlRoot>')";

        SqlCommand command2 = new SqlCommand(insertXmlQuery, sqlconnection);
        command2.ExecuteNonQuery();
    }
}

Thanks,
Morgan
Software Developer

Advertisements
Advertisements

1 comment:

  1. How to read multiple XML files from folder and insert to table.

    ReplyDelete