SQL XML datatype equivalent in C# .net

Question

I am currently parsing the data off an XML file and storing them onto a nvarchar field on the database. I am now looking to change NVarchar to XML. I am using Code First approach on EF Core, what is the XML equivalent data type in C# .net?

Some previous queries suggest that I can use String (C# Equivalent of SQL Server DataTypes), but some suggest something else(https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings?redirectedfrom=MSDN).

I am looking to clarify on what's the best approach for my query where I am not manipulating the XML content, I am merely parsing a file and inserting the data into the database.

Update:

I ended up using a String on C# .net as I didn't have to do any Xml manipulation, I copied the data from a file and sent it over as a String. Below is the code I used:

     var doc = new XmlDocument();
                doc.Load(file.FullName);

                if (doc.FirstChild.NodeType == XmlNodeType.XmlDeclaration)
                {
                    XmlDeclaration xmlDeclaration = (XmlDeclaration)doc.FirstChild;
                    if (String.Compare(xmlDeclaration.Encoding, Encoding.Unicode.WebName, StringComparison.OrdinalIgnoreCase) != 0)
                    {
                        xmlDeclaration.Encoding = Encoding.Unicode.WebName;
                    }
                }
                string XmlString = doc.OuterXml;

The data context automatically maps XML to `XElement` – GSerg Sep 30 '19 at 12:05 — GSerg, Sep 30 '19 at 12:05

score 0 · Accepted Answer · answered Sep 30 '19 at 11:59

I would avoid using a string directly - and especially in you case where you just need to store the data. Either go with SqlXml or something else that gives a reader. For example like this:

var param = cmd.Parameters.Add("@xml", SqlDbType.Xml);
var doc = XDocument.Load(xmlFilePath);
param.Value = doc.CreateReader();
cmd.ExecuteNonQuery();

(Connection and Command object left out).

The important advice here is just not to use a string object yourself, but as you have discovered already ymmv...

SQL XML datatype equivalent in C# .net

1 Answers1