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.)
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:
<?xml version="1.0" encoding="windows-1252"?>
elementI 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 String
s 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"?>
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 aString
!)
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;
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With