.NET: How to insert XML document into SQL Server

how to insert xml data into table in sql server
convert xml column to table sql server
insert xml data into sql table using c#
how to save xml data in sql server
sql server parse xml string
xml data type in sql server
insert xml file into sql table
import xml into sql server 2012

I want to insert arbitrary XML into SQL Server. The XML is contained in an XmlDocument object.

The column I want to insert into is either nvarchar, ntext, or xml column (If it makes your life easier then you can pick which type it is. Really it's an xml column.)

Prototype
void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{

}

The reason I ask is because I'm trying to find the proper way to turn the XmlDocument into something the database can take - being sure to keep the encodings right:

  • I have to make sure the encoding using during insert matches what the database takes
  • I have to synchronize the <?xml version="1.0" encoding="windows-1252"?> element

I know ntext, nvarchar, or xml are stored as UTF-16 inside SQL Server. So I have to be sure to give the data to SQL Server as UTF-16. This isn't a problem for Strings in .NET, since they are unicode UTF-16.

The 2nd problem, synchronizing the encoding attribute, is a tougher nut to crack. I have to figure out how to find the declaration element through the XmlDocument object:

<?xml version="1.0" encoding="windows-1252"?>   (or whatever the encoding may be)

and adjust it to UTF-16

<?xml version="1.0" encoding="UTF-16"?>

My naive attempt (that fails)

Ignoring the encoding in the XML declaration, and just figuring out how to save anything into SQL Server:

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{
   String sql = "INSERT INTO "+tableName+" ("+columnName+") 
          VALUES ('"+xmlToSave.ToString()+"')";

   using (DbCommand command = connection.CreateCommand())
   {
      command.CommandText = sql;

      DbTransaction trans = connection.BeginTransaction();
      try
      {
         command.ExecuteNonQuery();
         trans.Commit();
      }
      catch (Exception)
      {
         trans.Rollback();
         throw;
      }
   }
}

This fails because the sql I try to run is:

INSERT INTO LiveData (RawXML) 
VALUES ('System.Xml.XmlDocument')

This is because XmlDocument.ToString() returns "System.Xml.XmlDocument". Peeking at the implementation, it see that it literally is calling:

this.GetType().ToString();

Aside: Microsoft seems to have gone out of their way to prevent you from getting the Xml as a string - presumably because it leads to bugs (But they don't tell us what bugs, why they're bugs, or the right way to convert an XmlDocument into a String!)

See also

You have to use an SqlParameter. I would recommend doing it like that:

command.Parameters.Add(
   new SqlParameter("@xml", SqlDbType.Xml) 
       {Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                       , XmlNodeType.Document, null)) })

and the SQL should look like:

String sql = "INSERT INTO "+tableName+" ("+columnName+") VALUES (@xml)";

And since the first child is always the xml node, you can replace the encoding with the following statement.

xmlToSave.FirstChild.InnerText = "version=\"1.0\" encoding=\"UTF-16\"";

All in all it would look like that:

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName);
{
   String sql = "INSERT INTO "+tableName+" ("+columnName+") VALUES (@xml)";

   using (DbCommand command = connection.CreateCommand())
   {
      xmlToSave.FirstChild.InnerText = "version=\"1.0\" encoding=\"UTF-16\"";             
      command.CommandText = sql;
      command.Parameters.Add(
        new SqlParameter("@xml", SqlDbType.Xml) 
           {Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
                       , XmlNodeType.Document, null)) });


      DbTransaction trans = connection.BeginTransaction();
      try
      {
         command.ExecuteNonQuery();
         trans.Commit();
      }
      catch (Exception)
      {
         trans.Rollback();
         throw;
      }
   }
}

.NET: How to insert XML document into SQL Server, txt and insert an XML instance in the single-column table, sample table T . SQL Copy. INSERT INTO T  Step 3 – Importing the XML data file into a SQL Server Table. Now all we need is to make SQL Server read the XML file and import the data via the OPENROWSET function. This function is native to T-SQL and allows us to read data from many different file types through the BULK import feature, which allows the import from lots of file types, like XML.

The simplest solution is to use the OuterXml attribute of the document. I came across your question while trying to solve the problem under the condition that I could not executed a stored procedure. OuterXml returns the string of text you were expecting to get from .Value or .ToString()

void SaveXmlToDatabase(DbConnection connection, 
      XmlDocument xmlToSave, 
      String tableName, String columnName); 
{ 
   String sql = "INSERT INTO "+tableName+" ("+columnName+")  
          VALUES ('"+xmlToSave.OuterXml+"')"; 

   using (DbCommand command = connection.CreateCommand()) 
   { 
      command.CommandText = sql; 

      DbTransaction trans = connection.BeginTransaction(); 
      try 
      { 
         command.ExecuteNonQuery(); 
         trans.Commit(); 
      } 
      catch (Exception) 
      { 
         trans.Rollback(); 
         throw; 
      } 
   } 
} 

Bulk import & export of XML documents, Inserting element nodes into the document. The following example illustrates how to insert elements into a document. First, an XML document is  Use the following steps to create a table to receive the data that the XML Bulk Load component processes. Create a database named MyDatabase in SQL Server. Open SQL Query Analyzer, and then change the database to MyDatabase. Create a Customer table in MyDatabase by running the following SQL statement in Query Analyzer: USE MyDatabase.

Better late than never... I think you're looking for something like this:

void SaveXmlToDatabase(DbConnection connection,
      XmlDocument xmlToSave,
      String tableName, String columnName)
{
   String sql = "INSERT INTO "+tableName+" ("+columnName+") 
          VALUES (@XmlVal)";

   using (DbCommand command = connection.CreateCommand())
   {
      command.CommandText = sql;
      command.Parameters.AddWithValue("XmlVal", new SqlXml(new XmlNodeReader(xmlToSave)));

      DbTransaction trans = connection.BeginTransaction();
      try
      {
         command.ExecuteNonQuery();
         trans.Commit();
      }
      catch (Exception)
      {
         trans.Rollback();
         throw;
      }
   }
}

The XmlNodeReader object traverses and properly encodes the XmlDocument (or any other XmlNode), and the SqlXml object encapsulates that into the SqlDbType suitable for use with the parameter. This is safer and probably more efficient than using a string intermediary.

insert (XML DML), In this tip we look at a simple way to import a simple XML document into a SQL Server table. C. Inserting attributes into a document. The following example illustrates how attributes are inserted in a document. First, a document is assigned to an xml type variable. Then, a series of insert XML DML statements is used to insert attributes into the document. After each attribute insertion, the SELECT statement displays the result.

Just wanted to add an SQLConnection equivalent to Jonathan Overholt's solution:

private void StoreXMLInDatabase(XmlDocument doc)
{
    // xmlvalue is the name of the database column you want to insert into
    String sql = "INSERT INTO [tablename] (xmlvalue) VALUES (@xmlvalue)";
    // In order to read out you connection string from app.config, remember first to add a reference to System.Configuration in references,
    // and set using System.Configuration; in the top of the file
    string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;  
    using (SqlConnection conn = new SqlConnection(connString))
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            // Remember to specify the SqlTransaction *name* (transaction)
            SqlCommand cobj = new SqlCommand(sql, conn, transaction);
            cobj.CommandText = sql;
            cobj.Parameters.AddWithValue("xmlvalue", new SqlXml(new XmlNodeReader(doc)));
            cobj.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (SqlException ex)
        {
            //2627 = inserting duplicate key fail. Lets the procedure continue although an identity insert fail occurs
            if (ex.Number == 2627)
            {
                transaction.Rollback();
            }
        }
    } // end using
}

Tested OK in Visual studio 2017 and 2013 with .net up to version 4.6.2, and against MS SQL Server 2008 R2, 2012 and 2016

If an identity insert fail and you want to stop the procedure then, just insert a line after transaction.Rollback() like this:

throw;

Or you can stop the procedure in any circumstance by putting the transaction.Rollback(); and throw; lines just outside the conditional (the if)

Simple way to Import XML Data into SQL Server with T-SQL, In this tip we look at how you can parse an XML file and load into relational tables in SQL Server. Examples of bulk import and export of XML documents (SQL Server) 10/24/2016; 7 minutes to read +2; In this article. APPLIES TO: SQL Server 2016 and later Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . You can bulk import XML documents into a SQL Server database or bulk export them from a SQL Server database.

Importing and Processing data from XML files into SQL Server tables, Try to convert your xml string variable into XmlDocument and then pass that xml object to your Sql query as parameter. Hide Expand Copy  I want to pass xml document to sql server stored procedure such as this: CREATE PROCEDURE BookDetails_Insert (@xml xml) I want compare some field data with other table data and if it is matching that records has to inserted in to the table.

How to insert XML data in SQL server, Net Framework provides the Classes for read, write, and other operations in XML formatted files . Moreover the Dataset in ADO.NET uses XML format as its  Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line. I typically get around using the CONVERT function which allows me to instruct SQL Server to skip those instructions - use something like this:

Part 18 Load xml data into sql server table using sqlbulkcopy , Let's examine a situation where an XML document with complex hierarchical that ships with several Microsoft products including SQL Server and Access. to parse data from an XML document named customers.xml and create insert  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.

Comments
  • this question may be related: stackoverflow.com/questions/574928/…
  • I amended the SqlParameter Value which must be a value of type SqlXml
  • i don't think you meant xmlToSave.ToString(), since that returns the string "System.Xml.XmlDocument", which isn't valid XML.
  • Sure, you'r right. As far as I remember it must b. xmlToSave.Value
  • Ups - retestet it with an example ;) It's xmlToSave.InnerXml var doc = new XmlDocument(); doc.Load(@"d:\temp\test.htm"); Console.Out.WriteLine(doc.InnerXml);
  • Also added the replacement for the xml encoding ;)