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 SSMS
• Insert XML into SQL Table using SQL Parameter in C#
• Insert XML into SQL Table using SQL Query in C#

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]

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();
    }
}

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