Why does SqlClient use unnecessary XML conversion while passing SqlXml?
c#, sql, sql-server, sqlclient, xml
Solution
There is no converssion occuring in reallity. What you see is an artifact of Profiler/Trace having to somehow represent the TDS RPC call as a T-SQL batch you can copy paste and execute in SSMS. The real text executed is just `insert into XmlTable (dat) values(@data)` and `@data` type/value comes from the `ParamData` portion of the request. As with any wire protocol, serialization and encoding has to occur in the client and decoding and deserialization has to occur at the server. As a side note, there is no call to `sp_execute_sql` either, that is again an artifact of how Profiler/Trace displays an RPC call.
this code is used in high-load scenario and additional convertation makes it slower
That is speculation. You should base performance analysis on measurements and observations.
Problem
I`ve got a question about passing xml datatype to a query from C# code. First, here`s a table on SQL Server: ``` CREATE TABLE [dbo].[XmlTable]( [id] [int] IDENTITY(1,1) NOT NULL, [dat] [xml] NOT NULL ) ``` Second, I have an application which is written on C#. And I insert rows into the `XmlTable` from the application. Here`s the code: ``` XDocument doc = new XDocument(new XElement("root", new XElement("abc", "123"), new XElement("cba", "321"))); var reader = new XmlTextReader(new StringReader(doc.ToString())); var xml = new SqlXml(reader); using (var connection = new SqlConnection(_connectionString)) { using (var command = new SqlCommand("insert into XmlTable (dat) values(@data)", connection)) { command.Parameters.Add(new SqlParameter("@data", SqlDbType.Xml){Value = xml}); connection.Open(); command.ExecuteNonQuery(); } } ``` Everything works fine, rows are inserted. But if I run the SQL Profiler and inspect the insert command, I see following: ``` declare @p3 xml set @p3=convert(xml,N'<root><abc>123</abc><cba>321</cba></root>') exec sp_executesql N'insert into XmlTable (dat) values(@data)',N'@data xml',@data=@p3 ``` We can see that in spite of passing `SqlXml` type into a query `SqlClient` performs unnecessary conversion from string to XML. XML parser does waste work. The question is: why does `SqlClient` do this way? And how can I make it do without extra conversion?